SELECT (SELECT SUM(kwh) FROM energy_logger WHERE TIME >= '05:30:00' AND TIME < '18:30:00' GROUP BY Date) as daytime, (SELECT SUM(kwh) FROM energy_logger WHERE TIME >= '18:30:00' AND TIME < '22:30:00' GROUP BY Date) as peaktime, (SELECT SUM(kwh) FROM energy_logger WHERE TIME >= '22:30:00' OR TIME < '05:30:00' GROUP BY Date) offpeaktime, Date FROM energy_logger GROUP BY Date ORDER BY ID DESC
This is my query! And I always have an issue of this, “Subquery returns more than 1 row”…..
Advertisement
Answer
I think that you are looking for conditional aggregataion:
select date, sum(case when time '05:30:00' and time < '18:30:00' then kwh end) daytime, sum(case when time '18:30:00' and time < '22:30:00' then kwh end) peaktime, sum(case when time '22:30:00' or time < '05:30:00' then kwh end) offpeaktime from energy_logger group by date order by date
This will give you, for each day, the sum of kwh
over the 3 distinct time slots (daytime, peaktime, offpeaktime).