Skip to content
Advertisement

Is there anybody who can help me with mysql query?

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).

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement