Mysql Sum daily totals into weekly totals for given date range

Currently my db table has dailTotal values for every single day, I need to get these grouped into weekly totals.

Is it possible to add the daily totals up into weekly totals and display a row each weekly total between the given date range.

Im not quite sure where to start with this one. My current query to get is by day is:

SELECT dailyTotal
FROM   energyUsage
WHERE  meterIdentifier = '1300011505120'
      AND startTime >= '2017-01-01 00:00:00'
      AND startTime <= '2017-12-31 00:00:00';

Answer

Weeks can be a bit ambiguous. One handy definition is the built-in definition. So, you can do what you want using yearweek():

SELECT YEAREWEEK(startTime) as yyyyww, SUM(dailyTotal)
FROM  energyUsage
WHERE  meterIdentifier = '1300011505120' AND
       startTime >= '2017-01-01' AND
       startTime <= '2017-12-31'
GROUP BY yyyyww
ORDER BY yyyyww;

You can check out the mode argument to control whether weeks start on Sunday or Monday and when the first week of the year is. There is no mode that says: “the first week of the year starts on Jan 1st”, although you could put in logic for that purpose.

Note: If startTime has a time component, I am guessing you want:

WHERE  meterIdentifier = '1300011505120' AND
       startTime >= '2017-01-01' AND
       startTime < '2018-01-01'  -- note the inequality