Mysql Sum daily totals into weekly totals for given date range

Question

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';

score 1 · Accepted Answer · edited Mar 02 '23 at 14:20

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

SELECT YEARWEEK(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

If there is no rule for the year to start on Jan 1st what is it using in your query? As that is what I want — user794846, Jan 25 '17 at 15:07
@user794846 . . . Read about the `mode` argument . . . https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week. — Gordon Linoff, Jan 26 '17 at 02:53

Fuzzy · Answer 2 · 2017-01-25T15:06:24.820

0

This should do it:

SELECT SUM(dailyTotal) AS weeklyTotal
FROM   energyUsage
WHERE  meterIdentifier = '1300011505120'
      AND startTime >= '2017-01-01 00:00:00'
      AND startTime <= '2017-12-31 00:00:00'
GROUP BY YEAR(startTime),WEEK(startTime);

edited Jan 25 '17 at 15:06

answered Jan 25 '17 at 14:58

Fuzzy

3,810
2
15
33

This one doesnt seem to work: Unknown column 'weeklyTotal' in 'field list' – user794846 Jan 25 '17 at 15:04

score 0 · Answer 3 · answered Jan 25 '17 at 15:01

0

You could use an aggregate function:

SELECT dailyTotal,
       sum(dailyTotal) over (partition by EXTRACT(MONTH FROM StartTime)) as MonTotal
FROM   energyUsage
WHERE  meterIdentifier = '1300011505120'
      AND startTime >= '2017-01-01 00:00:00'
      AND startTime <= '2017-12-31 00:00:00';

I am assuming a lot of things here but you get the idea.

1- The version of MySQL supports aggregated functions

2- Your field is of date type

answered Jan 25 '17 at 15:01

Marco Polo

728
5
10

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by EXTRACT(MONTH FROM StartTime)) as MonTotal FROM energyUsage WHER' at line 2 – user794846 Jan 25 '17 at 15:05
what version of mysql allow over - partition? – McNets Jan 25 '17 at 15:09
$ mysql --version mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1 – Marco Polo Jan 25 '17 at 16:12
But you can reproduce it in MySQL. Look at http://stackoverflow.com/questions/11806528/does-mysql-have-the-equivalent-of-oracles-analytic-functions – Marco Polo Jan 25 '17 at 16:13

Mysql Sum daily totals into weekly totals for given date range

3 Answers3