DariusB

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.

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

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.

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.

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

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.

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

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

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

Can't thank you enough. That's superb. Best regards.

That's some very impressive stuff right there. Thank you very much for all your help and time

Also, have another question: I would like to calculate average number of total goals e.g. ( average of home_score + away score) in H2H matches of given teams from last 5 H2H matches. I can do that for single matches e.g. SELECT AVG(home_score + away_score) FROM championship WHERE home ="Blackburn Rovers" AND away = "Charlton Athletic" or home = "Charlton Athletic" and away = "Blackburn Rovers" ORDER BY match_date DESC Limit 5 Is there any way to have it done for all fixtures at once and displaying a table with fixtures and AVG(home_score + away_score) next to them and number of H2H matches of those teams? e.g. 15:00 Blackburn Rovers Charlton Athletic 2.3 3 15:00 Brentford Preston North End 0 0 15:00 Bristol City Reading 1.3 3 15:00 Huddersfield Town Bolton Wanderers 2 1 15:00 Hull City Queens Park Rangers 2.45 4 Thank you

That's superb. Thank you. Is there any way to display the table like the fixtures: 15:00 Blackburn Rovers Charlton Athletic 2.3 2.4 15:00 Brentford Preston North End 1.4 2.3 15:00 Bristol City Reading 2.6 0.9 15:00 Huddersfield Town Bolton Wanderers 3.6 1 15:00 Hull City Queens Park Rangers 0.8 1.65 Instead of the list of teams with averages? Thank you again.

Thank you for taking time to help me. I tried your code and that returned: 15:00 Blackburn 0.0000 15:00 Bolton 0.0000 15:00 Brentford 0.0000 15:00 Brighton0.0000 15:00 Bristol City 0.0000 etc. Did you get anything different?

Hello I have a database with matches for Championship and want to display a list of upcoming matches and average number of goals in matches for each of the teams from last 5 matches that they were involved in. E.g. SELECT ko_time, home, away FROM matches_table WHERE ko_time = TODAY will give me something like this. 15:00 Blackburn Rovers Charlton Athletic 15:00 Brentford Preston North End 15:00 Bristol City Reading 15:00 Huddersfield Town Bolton Wanderers 15:00 Hull City Queens Park Rangers Now, I know how to calculate an average number of goals for one team from their last 5 matches e.g. SELECT AVG(home_score + away_score) FROM matches_table WHERE home = "Brentford" or away = "Brentford" ORDER BY match_date DESC LIMIT 5 This will give me average number of goals from 5 last matches of Brentford. Now, I'm not sure how to combine these two queries and have the averages calculated for all teams and displayed like below: 15:00 Blackburn Rovers Charlton Athletic 2.3 2.4 15:00 Brentford Preston North End 1.4 2.3 15:00 Bristol City Reading 2.6 0.9 15:00 Huddersfield Town Bolton Wanderers 3.6 1 15:00 Hull City Queens Park Rangers 0.8 1.65 The table contains all matches - played and fixtures, which are updated once finished. Has anyone got any ideas? Thank you Attached link to db Database