CODEWITHSUNDEEP
phpmysqlsqldatabasepercentage
Rocksteady
Rocksteady

Reputation: 281

Group values and get percentage from database

I have a database table where I save players bets it the following logic:

enter image description here

This table will of course have several user_id's for every match_id. I want to display how the users have placed their bets. If they think that the home team will win lose or draw. AND I want it to be stated in percentage.

Germany - France 35% - 20% - 45%.

So far I have managed to calculate the quantity guesses, but not the percentage, with the following query:

SELECT games.home_team,games.away_team,COUNT(*) as 'qty',user_result_bet.match_id,(
CASE
WHEN `home_score` > `away_score` THEN 'home'
WHEN `home_score` < `away_score` THEN 'away'
ELSE 'draw'
END) as 'tipped_result'
FROM user_result_bet,GAMES
WHERE games.match_id = user_result_bet.match_id
GROUP BY tipped_result, match_id
ORDER BY match_id

Where do I go from here? I want to turn this in to percentage somehow? Im using PHP for the website

Upvotes: 2

Views: 623

Answers (2)

Wietze314
Wietze314

Reputation: 6020

SUM(CASE WHEN `home_score` > `away_score` THEN 1 ELSE 0 END)/COUNT(*) AS PercentageHome

Upvotes: 0

AdrianBR
AdrianBR

Reputation: 2588

you need to use count if

SELECT user_result_bet.match_id,COUNT(*) as 'qty',

count(if(`home_score` > `away_score`,1,null))/count(*)*100 as home_percent,
count(if(`home_score` < `away_score`,1,null))/count(*)*100 as away_percent,
count(if(`home_score` = `away_score`,1,null))/count(*)*100 as draw_percent
from wherever
group by 1

Upvotes: 1

Related Questions