CODEWITHSUNDEEP
mysqlsqlcumulative-sum
Danny W
Danny W

Reputation: 473

Sum of all rows prior to (and including) date on current row in MYSQL

It's important to know that the date will be unknown during the query time, so I cannot just hard code a 'WHERE' clause.

Here's my table:

+-----------+----------+-------------+
| Date_ID   | Customer | Order_Count |
+-----------+----------+-------------+
| 20150101  | Jones    | 6           |
| 20150102  | Jones    | 4           |
| 20150103  | Jones    | 3           |
+-----------+----------+-------------+

Here's the desired output:

+-----------+----------+------------------+
| Date_ID   | Customer | SUM(Order_Count) |
+-----------+----------+------------------+
| 20150101  | Jones    | 6                |
| 20150102  | Jones    | 10               |
| 20150103  | Jones    | 13               |
+-----------+----------+------------------+

My guess is I need to use a variable or perhaps a join.

Edit: still not able to get it fast enough. very slow.

Upvotes: 6

Views: 6196

Answers (4)

Hai Do
Hai Do

Reputation: 33

you can consider this solution select Date_ID, Customer, SUM(Order_COunt) over (order by Date_ID, Customer rows unbounded preceding) as SUM(Order_COunt) from table

Upvotes: 0

Samon
Samon

Reputation: 101

One way you could go about it is by using a sub query which sums all orders up till the current order. Probably not the fastest way, but it should do the trick.

SELECT `Date_ID`, `Customer`, 
    (SELECT sum(b.`Order_Count`) 
    FROM tablename as b WHERE 
    b.`Date_ID` <= a.`Date_ID` AND
    a.`customer = b.`Customer`) 
FROM tablename as a

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

Where performance is an issue, consider a solution akin the following:

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT i,@i:=@i+i FROM ints, (SELECT @i:=0)n ORDER BY i;
+---+----------+
| i | @i:=@i+i |
+---+----------+
| 0 |        0 |
| 1 |        1 |
| 2 |        3 |
| 3 |        6 |
| 4 |       10 |
| 5 |       15 |
| 6 |       21 |
| 7 |       28 |
| 8 |       36 |
| 9 |       45 |
+---+----------+

Upvotes: 1

jpw
jpw

Reputation: 44881

Try this query; it's most likely the best you can do without limiting the dataset you operate on. It should benefit from an index (customer, date_id).

select 
  t1.date_id, t1.customer, sum(t2.order_count)
from 
  table1 t1
left join 
  table1 t2 on t1.customer = t2.customer
           and t1.date_id >= t2.date_id
group by 
  t1.date_id, t1.customer;

Sample SQL Fiddle.

Upvotes: 4

Related Questions