Cumulative sum in MySQL View (calculating running profit/loss)

DariusB · November 6, 2015

Hi,

I have been struggling to get this ones working. I have a view from a couple of tables and wish to calculate running profit/loss in a separate column at the end.

The table from view looks like this:

country	Competition	match_date	ko_time	home	away	      Result	Profit

Europe	Europa League	05/11/2015	20:05	Sparta 	Schalke  	L	-10
Europe	Europa League	05/11/2015	18:00	Celtic	Molde	        W	 8
Europe	Europa League	05/11/2015	18:00	Plzen	Rapid Vienna	W	10
Bahrain	Premier League	05/11/2015	15:00	Manama 	East Riffa	L	-10
Denmark	Superliga	02/11/2015	18:00	Aarhus	Sonderjyske	L	-10

The matches are in the view from the newest to the oldest, so I would like the running profit/loss (cumulative sum) to start from the oldest match.

I have been trying different solutions, but it just displays 0's in the cumulative column.

SELECT .......
(@cum_sum := @cum_sum + 'Profit') AS "cumulative"
FROM mecze
JOIN (SELECT @cum_sum := 0.0)B
... conditions...
ORDER BY mecze.match_date DESC , mecze.ko_time DESC

The other methods that I found for calculating cumulative sum didn't work for me either.

Could anyone advice please?

Thank you

Edited November 6, 2015 by DariusB

Barand · November 6, 2015

try

SELECT country
,Competition
,match_date
,ko_time
,home
,away
,Result
,Profit 
,cum_profit
FROM mecze
INNER JOIN 
	(
    SELECT id
	, @cum:=@cum+profit as cum_profit
        FROM mecze
	    JOIN (SELECT @cum:=0) init
	ORDER BY ko_time, id
    ) cum USING (id)
ORDER BY ko_time DESC, id DESC;
+---------+----------------+------------+----------+--------+--------------+--------+--------+------------+
| country | Competition    | match_date | ko_time  | home   | away         | Result | Profit | cum_profit |
+---------+----------------+------------+----------+--------+--------------+--------+--------+------------+
| Europe  | Europa League  | 2015-11-05 | 20:05:00 | Sparta | Schalke      | L      |    -10 |        -12 |
| Denmark | Superliga      | 2015-11-05 | 18:00:00 | Aarhus | Sonderjyske  | L      |    -10 |         -2 |
| Europe  | Europa League  | 2015-11-05 | 18:00:00 | Plzen  | Rapid Vienna | W      |     10 |          8 |
| Europe  | Europa League  | 2015-11-05 | 18:00:00 | Celtic | Molde        | W      |      8 |         -2 |
| Bahrain | Premier League | 2015-11-05 | 15:00:00 | Manama | East Riffa   | L      |    -10 |        -10 |
+---------+----------------+------------+----------+--------+--------------+--------+--------+------------+

DariusB · November 6, 2015

Thank you so much for taking time helping me.

When I put your code to my query I get zeros again. Here is my full code:

SELECT mecze.country, mecze.competition, mecze.match_date, mecze.ko_time, mecze.home, mecze.away, mecze_staty_over25.avg_percent, mecze.match_score, IF( (
home_score + away_score
) > 2.5,  "W", IF( match_score =  "",  "",  "L" ) ) AS  "Result", IF( (
home_score + away_score
) > 2.5, FORMAT( (
mecze.odds_ft_over_25 -1
) *10, 2 ) ,  "-10" ) profit, cum_profit
FROM mecze
INNER JOIN 
	(
    SELECT id
	, @cum:=@cum+'profit' as cum_profit
        FROM mecze
	    JOIN (SELECT @cum:=0) init
	ORDER BY ko_time, id
    ) cum USING (id)
INNER JOIN mecze_staty_over25 ON mecze.match_id = mecze_staty_over25.match_id
WHERE mecze_staty_over25.avg_percent >59
AND mecze_staty_over25.h2h_success_per >49
AND mecze.odds_ft_over_25 >= 1.75
AND mecze.match_date >  "2015-10-08"
ORDER BY mecze.match_date DESC , mecze.ko_time DESC

//please note, adding order by "id DESC" at the end resulted in an error (Column 'id' in order clause is ambiguous)

And I get:

see result.jpg

P.S. How do you paste the table from phpmyadmin, as you do?

Thank you again

Barand · November 6, 2015

Mine relied on each row in mecze table having a unique "id" column.

My output is copy/pasted from mysql command line

DariusB · November 6, 2015

That's correct.

IS there any way that this code would work with my query, which is a join of two tables and without the id field?

Or would it be possible or needed to add id field for this query?

Thank you

Barand · November 6, 2015

If there's no id field, what is the primary key on that table?

DariusB · November 7, 2015

Both tables are joined on

mecze.match_id = mecze_staty_over25.match_id

There is an id for main table (mecze), but if I understand it right, then the primary key is on match_id.

DariusB · November 7, 2015

Ok, I got something to work... The reason that I got 0's was that it didn't like the field 'profit' and I replaced it with the formula for profit.

I still need a small correction though, as it calculates it from the top and I need it from the back.

This is my code:

SELECT mecze.country, mecze.competition, mecze.match_date, mecze.ko_time, mecze.home, mecze.away, mecze_staty_over25.avg_percent, mecze.match_score, IF( (
home_score + away_score
) > 2.5,  "W", IF( match_score =  "",  "",  "L" ) ) AS  "Result", 
IF( (home_score + away_score) > 2.5, FORMAT( (mecze.odds_ft_over_25 -1) *10, 2 ) ,  "-10" ) Profit,
(@cum_sum:=@cum_sum+ (IF( (home_score + away_score) > 2.5, FORMAT( (mecze.odds_ft_over_25 -1) *10, 2 ) ,  "-10" ))) as "cumulative"
FROM mecze
JOIN (select @cum_sum := 0.0) B order by match_date DESC
INNER JOIN mecze_staty_over25 ON mecze.match_id = mecze_staty_over25.match_id
WHERE mecze_staty_over25.avg_percent >59
AND mecze_staty_over25.h2h_success_per >49
AND mecze.odds_ft_over_25 >= 1.75
AND mecze.match_date >  "2015-10-08"
ORDER BY mecze.match_date DESC , mecze.ko_time DESC

How do I get it to add up cumulative sum from last field, as it it's in your solution?

Thank you

Barand · November 7, 2015

Well, you've certainly moved the goal posts since your original post on which my solution was based.

Provide an SQL data dump of the necessary table, so I don't waste more of my time setting up test data, and I will look at it again for you.

DariusB · November 7, 2015

Apologies. As a newbie, I'm all over the place at times.

Please find attached database with both tables and created view, which need the cumulative profit column added (starting adding from the bottom - the oldest match).

http://s000.tinyupload.com/?file_id=58142655694333474165

Hope that's it's all ok. When I tried the other query again, it doesn't work in the VIEW as it has a variable or parameter.

So it may need doing with with something like this:

 SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

Sadly I couldn't get it right in my case.

Thank you so much for your time.

Best Regards

P.S. I added link to my sample sql database file, as it wouldn't let me attach it here as .sql not .rar.

Let me know, if you need anything else.

Edited November 7, 2015 by DariusB

Barand · November 7, 2015

I added "match_id" from your mecze table to your current "cumulative_profit" view then ran my query against that view instead of the mecze table. You have already done the hard work to calculate the profit in that view.

SELECT country
,Competition
,match_date
,ko_time
,home
,away
,Result
,Profit 
,cum_profit
FROM cumulative_profit
INNER JOIN 
	(
    SELECT match_id
	, @cum:=@cum+profit as cum_profit
        FROM cumulative_profit
	    JOIN (SELECT @cum:=0) init
	ORDER BY ko_time, match_id
    ) cum USING (match_id)
ORDER BY ko_time DESC, match_id DESC;

results

+---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+
| country | competition    | match_date | ko_time | home              | away         | Result | Profit | cum_profit |
+---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+
| England | Premier League | 2015-11-07 | 15:00   | Leicester         | Watford      | W      | 8.70   |     -20.28 |
| England | Premier League | 2015-11-07 | 15:00   | West Ham          | Everton      | L      | -10    |     -28.98 |
| England | Championship   | 2015-11-02 | 15:00   | Bolton            | BristolCity  | L      | -10    |     -18.98 |
| England | Championship   | 2015-11-05 | 15:00   | Blackburn         | Brentford    | W      | 8.82   |      -8.98 |
| England | Premier League | 2015-11-07 | 15:00   | Manchester United | West Brom    | L      | -10    |      -17.8 |
| England | Premier League | 2015-11-07 | 15:00   | Norwich           | Swansea      | L      | -10    |       -7.8 |
| England | Premier League | 2015-11-07 | 15:00   | Sunderland        | Southampton  | L      | -10    |        2.2 |
| England | Championship   | 2015-11-06 | 12:30   | Huddersfield      | Leeds        | W      | 12.20  |       12.2 |
+---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+

DariusB · November 8, 2015

Thank you. That seems to work, but is no good for my purpose, as it won't let me save it as a VIEW.

I will try to use the VIEW that you suggested with the added match_id and maybe the other code that I pasted above.

DariusB · November 8, 2015

Trying to use

 SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

in my case, but I can't get it working.

Need something that could save as view....

View's SELECT contains a subquery in the FROM clause

Barand · November 8, 2015

Need something that could save as view....

I that case you have a problem as views don't like subqueries or @variables. Basically, don't store derived values, run the query when you want the cumulatives

DariusB · November 8, 2015

It's for presentation of the data and it updates every day, so I will need some solution to store it for me as view. Also, will need to use it for many different tables.

I will try to look for other solutions.

Thank you very much for all your help and time spent.

Barand · November 8, 2015

Correction:

I forgot you had separate date and time fields. The ORDER BY clauses in my query need to include the date too.

Sign In

Cumulative sum in MySQL View (calculating running profit/loss)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information