Incremental Count(*) using YEARWEEK

Question

I have a bunch of data stored in a table, each row ends with a ts column, type TIMESTAMP.

I want to get incremental counts up until a point, so for instance I have the following query:

SELECT YEARWEEK(ts), DATE(ts), COUNT(*) FROMorderWHERE DATE(ts) >= '01/12/13' GROUP BY YEARWEEK(ts)

Which produces something like:

201346  20/11/2013  59
201347  24/11/2013  44
201348  01/12/2013  21

However I need a column that adds up the COUNTS up until that point, so I'd need something like:

201346  20/11/2013  59 59
201347  24/11/2013  44 103
201348  01/12/2013  21 124

How can I achieve this with mysql?? It's for a line graph, so I need to show that the numbers go up each week and I can't do that with the current SQL statement.

Can you give a look at http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql — etsa, May 08 '17 at 08:44

score 1 · Accepted Answer · edited May 23 '17 at 12:26

1

SET @SUM:=0;
SELECT YEARWEEK(ts), DATE(ts), COUNT(*),(@SUM := @SUM+COUNT(*))  as CSUM
FROM orders WHERE DATE(ts) >= '01/12/13' GROUP BY YEARWEEK(ts)

courtesy this answer from Andomar

edited May 23 '17 at 12:26

Community

1
1

answered May 08 '17 at 08:44

Muhammad Bashir

97
8

@Chud37 This is not the right answer. If you carefully see the answer linked in this, you'll see why it's wrong. – Gurwinder Singh May 08 '17 at 09:01

score 0 · Answer 2 · answered May 08 '17 at 08:51

0

You can use user variables to get the running count:

set @total := 0

select YEARWEEK(ts),
    date(ts),
    COUNT(*),
    @total := @total + COUNT(*) as running_count
FROM order
WHERE date(ts) >= '01/12/13'
group by YEARWEEK(ts)
order by YEARWEEK(ts);

answered May 08 '17 at 08:51

Gurwinder Singh

38,557
6
51
76

Incremental Count(*) using YEARWEEK

2 Answers2