php - 对值进行分组并从数据库中获取百分比

Question

我有一个数据库表，我按照以下逻辑保存玩家投注:

对于每个 ma​​tch_id，此表当然会有多个 user_id。我想显示用户如何下注。如果他们认为主队会赢，输或平。我希望它以百分比表示。

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

到目前为止，我已经通过以下查询设法计算出数量猜测，但不是百分比:

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

我从这里去哪里？我想以某种方式将其转换为百分比？我在网站上使用 PHP

Answer 1

你需要使用计数如果

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