Mysql Query SUM adding each to eachother result?

Question

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:

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

don't apologize, it's an interesting question. not sure if/how it can be done, though. as you kind of suggest, it would be easy to create that functionality outside mysql once the values are returned — Nicolas78
– Nicolas78, Commented Apr 19, 2014 at 22:35
well if it can't be done with mysql only. i would love to see how it would be done in php as well. But I am using the mysql loop as a Json output, but I can do it from the php later. — user2639176
– user2639176, Commented Apr 19, 2014 at 22:40

Joachim Isaksson · Accepted Answer · 2014-04-19 22:43:45Z

2

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.

answered Apr 19, 2014 at 22:43

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2639176 Over a year ago

thanks, this seems like a nice result. I'll test this now to use mysql only.

CodeBird · Accepted Answer · 2014-04-19 23:09:51Z

0

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;

answered Apr 19, 2014 at 23:09

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Mysql Query SUM adding each to eachother result?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related