CODEWITHSUNDEEP
mysqlsql
Kasper Hansen
Kasper Hansen

Reputation: 6557

Sums over individual days

Here I have pulled out the consumption of gas for the first day of 2007. The time is in UTC, so the day starts from 5 in the morning and runs for 24 hours.

select sum(b.kwh) as consumption
from BATimeSeries b
where 
    b.companyNo = 1 and 
    b.DayTime >= '2007-01-01 05:00:00.000' and 
    b.DayTime < '2007-01-02 05:00:00.000'

Here it just gives a table with a single row (consumption) which gives the consumption in kWh for that day. But I would really like to have a table that spans over a period of time, for example a week, month or year. Each row has two columns: day, consumption, which lists each day in the interval and the sum of consumption for that specific day. I can't figure out to aggregate over days and then for each of these days to give the sum for that day. Any help is appreciated.

Upvotes: 0

Views: 52

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

A simple GROUP BY should suffice;

SELECT DAY(daytime - INTERVAL 5 HOUR) day, SUM(b.kwh) consumption
FROM BATimeSeries b
WHERE b.companyNo = 1
GROUP BY day;

An SQLfiddle to test with.

Upvotes: 2

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Something like this?

SELECT DATE(b.DayTime),sum(b.kwh) as consumption
FROM BATimeSeries b
WHERE
    b.companyNo = 1 and 
    b.DayTime >= '2007-01-01 05:00:00.000' and 
    b.DayTime < '2007-01-02 05:00:00.000'
GROUP BY DATE(b.DayTime)

If you remove the where, you should get from all the days.

SELECT DATE(b.DayTime),sum(b.kwh) as consumption
FROM BATimeSeries b
WHERE b.companyNo = 1 and 
GROUP BY DATE(b.DayTime)

Upvotes: 1

Related Questions