Finding a date from a SUM

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!

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.

Something like having clause

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

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