Answer to Question #61145 in Databases | SQL | Oracle | MS Access for Kate

Question #61145

The table:

_date(2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06)
_period(1,2,3,4,5,1,2,3,4,5,1,2, 1,2,3,4,5,1,2,3,4,5)
_id(x1,x1,x1,x1,x1,x2,x2,x2,x2,x2,x3,x3,x4,x4,x4,x4,x4,x5,x5,x5,x5,x5)
_reading(1,1,1,1,1,2,2,2,2,2,5,6;1,1,1,1,1,2,1,1,1,1)
_reading2 (5,5,5,5,5,4,4,4,4,4,7,7;1,1,1,1,1,1,1,1,1,1)

This should be the answer:

_date(2014-03-05;2014-04-05)
sum_reading(15;11)
sum_reading2 (9;2)

I have this query:

SELECT A._date, sum(_Reading) as _Reading,
sum(Distinct _Reading2) as _Reading2
FROM table A
INNER JOIN (SELECT _ID, _date
FROM table
GROUP BY _ID, _date
HAVING count(_Period) = 5) B
on A._ID = B._ID
and A._Date = B._Date
GROUP BY A._Date

but the result is wrong.

Expert's answer

select _date, _id, sum(_reading), sum(_reading2)
from table2
group by _id
having count(_period) = 5