MySQL how to create a running total

Question

I have some data in table_1:

order_id | cust_id | order_date | city_id
101        |    1    | 15/03/2018 |     1001
102        |    1    | 15/03/2018 |     1005
103        |    2    | 10/03/2018 |     1001
104        |    4    | 16/02/2018 |     1006
105        |    4    | 10/01/2018 |     1250
106        |    4    | 15/03/2018 |     1250
107        |    6    | 16/02/2018 |     1058
108        |    6    | 10/03/2018 |     1058
109        |    4    | 23/02/2018 |     1006
110        |    7    | 19/01/2018 |     1005
111        |    7    | 21/01/2018 |     1005
...

I have this data in table_2

city_id | city_name
  1001  |   New York
  1005  |  London
  1006  |  Brighton
  1250  |  Toronto
  1058  | Manchester

I need to find the weekly order count for London for the last 10 weeks, and also the cumulative total.

This is just a subset of the data I'm working with.

So far I've tried this:

set @running_total:=0;
select week(a.order_date) as week_start,
    count(a.order_id) as order_count,
    (
        @running_total := @running_total + count(a.order_id)
        ) as cuml_count
from table_1 a
    left join table_2 b on a.city_id = b.city_id
    join (SELECT @running_total := 0) r
where b.city_name = "London"
group by 1
;

but the resulting cuml_count matches the order_count. On the data I'm working with it looks like:

week_start | order_count | cuml_count
     2     |      1      |      1
     3     |      1      |      1
     10    |      1      |      1

It should look like:

week_start | order_count | cuml_count
         2     |      1      |      1
         3     |      1      |      2
         10    |      1      |      3

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Mar 16 '18 at 17:08
That's odd and interesting, it is the first time in 20+ years that I'm seeing someone using a number as alias. Now I will test this and also using it along with the columns numbers in order by just to see what happens! — Jorge Campos, Mar 16 '18 at 17:37
Well, Mysql doesn't accept this syntax. Is this the real query? — Jorge Campos, Mar 16 '18 at 17:39
@JorgeCampos I had to hide the actual data and table names, I will replace replace with `select a.order_id, b.city_id from table_1 a, table_2 b` etc. — jceg316, Mar 16 '18 at 17:40

Dave Goten · Accepted Answer · 2018-03-20T13:31:10.573

2

You can see this SO post for the details Calculate a running total in MySQL

But something like:

SET @running_total:=0;
SELECT 
    week_start, 
    order_count,
    (@running_total := @running_total + order_count) AS cuml_count
FROM (
    SELECT week(t1.order_date) as week_start,
    COUNT(t1.order_date) AS order_count
    FROM table_1 AS t1
        LEFT JOIN
            table_2 AS t2 
            ON t1.city_id = t2.city_id
    WHERE t2.city_name = "London"
    GROUP BY week_start
) AS temp
ORDER BY week_start

might work for you

Edit: http://sqlfiddle.com/#!9/f8f806/5 Created one for OP & added ORDER BY

Edit: Moved to @Strawberry's placement of the ORDER BY, the init in select is also really good too!

edited Mar 20 '18 at 13:31

answered Mar 16 '18 at 17:41

Dave Goten

701
4
13

I cannot imagine why an OUTER JOIN would appear in such a query – Strawberry Mar 16 '18 at 17:44
I was just meaning to point out that this might be a duplicate question is all. But the formatting is a bit nicer here than in comments – Dave Goten Mar 16 '18 at 17:46
Unfortunately, the accepted answer on the linked page is flawed. It happens, I'm afraid. This answer perpetuates the error. – Strawberry Mar 16 '18 at 17:47
This worked, thanks very much. I've actually tried the other solution linked to but it didn't work. – jceg316 Mar 16 '18 at 17:50
2

@jceg316 If this works, it does so by luck rather than design. A variable rank ALWAYS needs an ORDER BY clause. – Strawberry Mar 16 '18 at 17:53
@Strawberry hmm, good point, there really should be an `order by` in there shouldn't there. – Dave Goten Mar 16 '18 at 17:55
1

It's still not correct. Here's a link to a query. I can't say whether it's correct, but it is at least valid: http://sqlfiddle.com/#!9/f8f806/7 – Strawberry Mar 16 '18 at 19:22

MySQL how to create a running total

1 Answers1