CODEWITHSUNDEEP
mysql
MontrealDevOne
MontrealDevOne

Reputation: 1044

MySQL Percentage Divide count by sum

I have a table games, I have fields name, gameStatus, and created_at

This is the result I get

| name    | total   | percentage |   
| Caleb   | 13992   | 11.8372    |    
| Fabian  | 27570   | 23.3241    |    
| Frank   | 7181    | 6.0751     |    
| Francis | 19897   | 16.8328    |    
| Felix   | 4319    | 3.6539     |    
| Gary    | 1       | 0.0008     |    
| George  | 2405    | 2.0346     |    
| Gavin   | 16144   | 13.6577    |    
| Gab     | 22076   | 18.6762    |    
| Nigel   | 367     | 0.3105     |    
| Peter   | 2465    | 2.0854     |    
| Troy    | 1787    | 1.5118     |

from SQL query

SELECT 
  name,
  total,
  (total/total_sum * 100) AS percentage
FROM    
  (SELECT 
    name,
    COUNT(*) AS total
  FROM
    games 
  WHERE  gameStatus = 'win' 
    AND (
      created_at > '2018-03-01 12:02:26'
    ) 
  GROUP BY name) AS T  
  CROSS JOIN (SELECT 
    COUNT(*) AS total_sum
  FROM
    games 
  WHERE gameStatus = 'win'
    AND (
      created_at > '2018-03-01 12:02:26'
    ) ) TS;

In short, I want to find out from all the games won, what percentage did each player win. I would like to do this without having to run two SQL subqueries.

SQL fiddle http://sqlfiddle.com/#!9/bbbaa5/3

Upvotes: 0

Views: 890

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

As I was indicating in my comment, both parts do not need to be subqueries.

  SELECT name
     , COUNT(*) AS total
     , 100 * COUNT(*)/s.total_sum AS percentage
  FROM games 
  CROSS JOIN (
     SELECT COUNT(*) AS total_sum 
     FROM games 
     WHERE gameStatus = 'win' 
       AND created_at > '2018-03-01 12:02:26'
  ) AS s
  WHERE gameStatus = 'win' 
    AND created_at > '2018-03-01 12:02:26'
  GROUP BY name
  ;

Not sure if this will actually be any faster though; I wouldn't be surprised if MySQL ends up handling them in nearly identical manners.

Upvotes: 1

Related Questions