CODEWITHSUNDEEP
mysqlsqldatabase
Tonven
Tonven

Reputation: 626

How to find percentages of values in SQL?

I use following query to find out percentage of winning by Hero in games:

SELECT Hero, (COUNT(*) * g.games) AS p
FROM gamestatistics
JOIN (
SELECT 100/COUNT(*) AS games
FROM gamestatistics
) AS g
WHERE PointEarned>0
GROUP BY Hero
ORDER BY p DESC

But this gives me percent for ALL games. And I want to know

percentage(for Hero)=(Won games by this player)/(Played games by this hero)

instead of

percentage(for Hero)=(Won games by this player)/(All games)
Thanks.

Upvotes: 0

Views: 239

Answers (3)

Yogendra Singh
Yogendra Singh

Reputation: 34367

Use count(HERO) in place of count(*) as I am thinking its not able to perform the group by properly.

  SELECT Hero, (COUNT(HERO) * g.games) AS p
  FROM gamestatistics
  JOIN (
        SELECT 100/COUNT(*) AS games
        FROM gamestatistics
  ) AS g
  WHERE PointEarned>0
  GROUP BY Hero
  ORDER BY p DESC;

EDIT: I believe you wanted sum of PointsEarned to be divided by count. In that case the query is little changed as below:

SELECT Hero, (SUM(case when PointEarned <1 then 0 else 1 end)*100/count(HERO)) AS p
FROM gamestatistics
GROUP BY Hero
ORDER BY p DESC;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425261

SELECT  hero, AVG(pointEarned > 0) p
FROM    gamestatistics
GROUP BY
        hero
ORDER BY
        p DESC

Upvotes: 4

roman
roman

Reputation: 117345

Try this

select
   gc.Hero,
   sum(case when gc.PointEarned > 0 then 1 else 0 end) / count(*) as p
from gamestatistics as gc
group by by gc.Hero
order by p desc

Upvotes: 2

Related Questions