CODEWITHSUNDEEP
mysql
jceg316
jceg316

Reputation: 489

MySQL how to create a running total

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

Upvotes: 0

Views: 48

Answers (1)

Dave Goten
Dave Goten

Reputation: 701

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!

Upvotes: 2

Related Questions