mysql Group by week and add totals to every week

Question

I'm trying to create a query that will return totals of a number based on every week so I can create a rising trend line chart. In my table I have a number of records that record a completion date (completed). I'd like to be able to create a query that generates a rolling total every week. So if week 1 there are 10 completed, week 2 there are 15 completed, and week 3 has 5 completed the desired result would be:

Week 1 totals: 10 Week 2 totals: 25 Week 3 Totals: 30

Sample data:

 id status  sched
 12 Successful  2017-04-04 00:00:00.000
 15 Successful  2017-06-20 19:30:00.000
 18 Successful  2017-10-17 18:00:00.000
 26 Successful  2017-04-05 00:00:00.000
 29 Successful  2017-06-16 00:00:00.000
 30 Successful  2017-04-06 00:00:00.000
 31 Successful  2017-04-07 00:00:00.000
 32 Successful  2017-04-06 00:00:00.000
 34 Successful  2017-10-18 18:00:00.000
 35 Successful  2017-06-13 00:00:00.000

This is the query I'm using to successfully generate data BY WEEK without any rollups. I tried adding "WITH ROLLUP" but it only gave the grand total at the end, not week by week.

select DATE_FORMAT(completed,'%d/%m/%Y') AS nd , wk, count(*)as totals
from
(
   select id, completed, yearweek(completed)as wk from w10_upgrades
   where status = 'Successful' and type = 'Normal'
   and yearweek(completed) is not null
) x
GROUP BY wk
ORDER BY wk;

Desired output:

 wk     totals
 201714   10
 201715   25 (output would = week 201714 + 201715)
 201716   55 (output would = week 201714 + 201715 + 201716)
 ect...

Any direction is appreciated. I can't find anything related to this.

please show your schema, sample source data and expected output. Thanks. — ADyson, Jul 25 '18 at 14:17
Something like this might help you: https://stackoverflow.com/a/1290936/3044080 — nomistic, Jul 25 '18 at 14:32

score 0 · Answer 1 · answered Jul 25 '18 at 16:59

Final result

SET @runtot:=0;
SELECT
   DATE_FORMAT(completed,'%d/%m/%Y') AS niceDate,
   wk,
   (@runtot := @runtot + c) AS rt
FROM
   (SELECT
        completed,
   yearweek(completed)as wk,
   COUNT(*) AS c
FROM  `w10_upgrades`
where status = 'Successful' and type = 'Normal'
and yearweek(completed) is not null
GROUP  BY wk
ORDER  BY wk) x

mysql Group by week and add totals to every week

1 Answers1