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

Question

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.

Answer 1

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.