Sum column values by group by date

Question

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?

Answer 1

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;