I have these two tables:
First (System):
ID GROUP
---------
1 1
2 1
3
4 1
Second (Values):
SYS_ID DATE VALUE
------------------
1 23 3
1 21 5
3 20 7
4 19 9
3 19 11
2 23 13
I need to find out sum of VALUE in one column for a unique date for all SYS_ID's in a particular group within a specified date-range.
Like (for group '1' and date ( >=20 & <25 ) ):
DATE VALUE
-----------
21 5
23 16
The closest I got is
DATE VALUE
-----------
21 5
23 3
23 13
with this
select
val.date, val.value
from
values as val
inner join
(select id from system where group = 1) as sys on (val.sys_id = sys.id)
where
(val.date >= 21 and val.date < 25)
group by
val.date, sys.id
which, on trying to remove 'SYS.ID' from 'GROUP BY' throws error saying 'VAL.VALUE' needs to be in the 'GROUP BY' clause.
Any suggestions?
I think you just need to fix the group by and add sum():
select v.date, sum(v.value)
from values v inner join
system s
on s.id = val.sys_id
where s.group = 1 and v.date >= 21 and v.date < 25
group by val.date;