user2639176
user2639176

Reputation: 91

Mysql Query SUM adding each to eachother result?

Sorry if my question makes no sense. Not sure if we can do this with mysql only. Lets say I have this query:

SELECT SUM(win) * 100 as win_profit, date, uid FROM `tips` WHERE uid = 60 AND placed = 1 GROUP by date

This would obviously get the sum of the win column each day that is in the database.

Lets say the database had:

|___win___|____date____|
|   10    | 2014-04-16 |
|   10    | 2014-04-16 |
|   10    | 2014-04-17 |
|   10    | 2014-04-18 |
|   10    | 2014-04-18 |
|   10    | 2014-04-18 |
|   10    | 2014-04-19 |
|   10    | 2014-04-19 |
|   10    | 2014-04-19 |

This would result:

20
10
30
30

How can I get it to result so each adds up, mysql query only. So the result would be:

20
30
60
90

Upvotes: 1

Views: 69

Answers (2)

CodeBird
CodeBird

Reputation: 3858

This could be another way to do it...

SET @full_sum=0;
SELECT @full_sum+SUM(win) as win_profit, date as this_date, uid, 
@full_sum:=(SELECT @full_sum+SUM(win) 
            FROM `testing` WHERE uid = 60 
            GROUP by date HAVING date=this_date) 
FROM `testing` WHERE uid = 60 GROUP by date;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

You could get all distinct dates, and LEFT JOIN to find the sum of all values up to that date; I kept the 100 multiplier from your sample query, but you need to remove it to get a result matching your desired result.

SELECT 100 * SUM(b.win), a.date 
FROM (SELECT DISTINCT date FROM `tips`) a
LEFT JOIN tips b ON a.date >= b.date
GROUP BY a.date
ORDER BY a.date

An SQLfiddle to test with.

Upvotes: 2

Related Questions