CODEWITHSUNDEEP
mysqlsql
user794846
user794846

Reputation: 1931

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

Upvotes: 1

Views: 1115

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

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

Upvotes: 0

Marco Polo
Marco Polo

Reputation: 738

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

Upvotes: 0

Related Questions