DariusB Posted November 6, 2015 Share Posted November 6, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 6, 2015 Share Posted 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 | +---------+----------------+------------+----------+--------+--------------+--------+--------+------------+ Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 6, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 6, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 6, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 6, 2015 Share Posted November 6, 2015 If there's no id field, what is the primary key on that table? Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 7, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 7, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 7, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 7, 2015 Author Share Posted November 7, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 7, 2015 Share Posted 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 | +---------+----------------+------------+---------+-------------------+--------------+--------+--------+------------+ 1 Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 8, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 8, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 8, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
DariusB Posted November 8, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 8, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.