Jump to content

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


DariusB

Recommended Posts

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 by DariusB
Link to comment
Share on other sites

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 |
+---------+----------------+------------+----------+--------+--------------+--------+--------+------------+
Link to comment
Share on other sites

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

 

post-179776-0-31513400-1446837435_thumb.jpg

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by DariusB
Link to comment
Share on other sites

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 |
+---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.