Finding a date from a SUM

Question

I am having trouble finding what date my customers hit a certain threshold in how much money they make.

customer_id | Amount | created_at
---------------------------
1134       | 10   | 01.01.2010    
1134       | 15   | 02.01.2010    
1134       | 5    | 03.24.2010    
1235       | 10   | 01.03.2010    
1235       | 15   | 01.03.2010    
1235       | 30   | 01.03.2010    
1756       | 50   | 01.05.2010    
1756       | 100  | 01.25.2010

To determine how much total amount they made I run a simple query like this:

SELECT customer_id, SUM(amount) 
FROM table GROUP BY customer_id

But I need to be able to find for e.g. the date a customer hits $100 in total amount.

Any help is greatly appreciated. Thanks!

score 1 · Answer 1 · edited May 23 '17 at 09:58

1

Jesse,

I believe you are looking for a version of "running total" calculation.

Take a look at this post calculate-a-running-total. There is number of useful links there.

This article have a lot of code that you could reuse as well: http://www.sqlteam.com/article/calculating-running-totals.

edited May 23 '17 at 09:58

Community

1
1

answered Jan 07 '11 at 16:53

Ilya Berdichevsky

1,249
10
24

Thanks, this has helped a lot. – Jesse Lung Jan 07 '11 at 17:18

score 0 · Answer 2 · answered Jan 07 '11 at 16:14

0

Something like having clause

SELECT customer_id, SUM(amount) as Total FROM table GROUP BY customer_id having Total > 100

answered Jan 07 '11 at 16:14

TalentTuner

17,262
5
38
63

Hm, I think that only returns customers that have over $100 in total amount. I need to find the exact date they hit $100, ie. if a customer has $150 in total amount today, I need to find they date they passed $100. – Jesse Lung Jan 07 '11 at 16:18

score 0 · Answer 3 · answered Jan 07 '11 at 17:01

I'm not sure if MySQL supports subqueries, so take this with a grain of salt:

SELECT  customer_id
      , MIN(created_at) AS FirstDate
FROM    ( SELECT    customer_id
                  , created_at
                  , ( SELECT    SUM(amount)
                      FROM      [Table] t
                      WHERE     t.CustomerID = [Table].CustomerID
                                AND t.created_at <= [Table].created_at
                    ) AS RunTot
          FROM      [Table]
        ) x
WHERE   x.RunTot >= 100
GROUP BY customer_id

Finding a date from a SUM

3 Answers3