CODEWITHSUNDEEP
sqlmysqlcount
James Simpson
James Simpson

Reputation: 13688

Select 2 COUNT()'s from MySQL query

I am working on a rankings page for a game and am looking to order the rankings first by wins, and then by losses (in case of people having the same number of wins). The following query works fine in order to make a list in order by wins, but I am not sure how to put losses into this query.

SELECT username, COUNT(id) AS wins 
  FROM tblBattleHistory 
  WHERE battle_type = '0' && outcome = '1' 
  GROUP BY username 
  ORDER BY wins DESC

outcome = '1' means they won, so when outcome = '0' then that means they lost.

Upvotes: 1

Views: 2668

Answers (5)

Bobby
Bobby

Reputation: 11576

This might also work:

SELECT
    username,
    SUM(IF(outcome = 1, 1, 0)) AS 'wins',
    SUM(IF(outcome = 0, 1, 0)) AS 'losses'
FROM
    tblBattleHistory;

It's basically counting the occurences of outcome = 1 and outcome = 0.

Upvotes: 1

Thorsten
Thorsten

Reputation: 13181

Here's my idea:

SELECT username, SUM (CASE WHEN outcome = '1' Then 1 Else 0 End) As Wins,
    SUM (CASE WHEN outcome = '0' Then 1 Else 0 End) As Losses
FROM tblBattleHistory 
WHERE battle_type = '0'
GROUP BY username 
ORDER BY wins DESC, Losses ASC

(Depending on your DBMS, you may have to repeat the SUMs in the Order By rather than use the aliases.)

This also allows to to come up with some stranger points schemes, for example for German football (win=3, tie=1 point)

SELECT username, SUM (CASE
    WHEN outcome = '1' Then 3 
    WHEN outcome = '2' Then 1 /* 2 is a tie */
    ELSE 0 End) As Points
etc.

Upvotes: 4

Trav L
Trav L

Reputation: 15192

SELECT username, losses, wins
FROM (
    SELECT username, COUNT(id) AS wins 
    FROM tblBattleHistory
    WHERE battle_type = '0' && outcome = '1' 
    GROUP BY username
) A
LEFT JOIN (
    SELECT username, COUNT(id) as losses
    FROM tblBattleHistory
    WHERE battle_type = '0' && outcome = '0'
) B ON A.username = B.username
ORDER BY wins DESC, losses ASC

This may not be an efficient query but does the job.

Upvotes: 0

Greg
Greg

Reputation: 321588

You can do it like this:

SELECT username, SUM(outcome) AS wins, COUNT(*) - SUM(outcome) AS losses
  FROM tblBattleHistory 
  WHERE battle_type = '0'
  GROUP BY username 
  ORDER BY wins DESC, losses

Upvotes: 8

COTOHA
COTOHA

Reputation: 243

I undestand that my answer is obviosly not what you asked, but... i think instead of getting counts from tblBattleHistory you should add 2 fileds to you Users table (wins, fails) and update them (maybe via insert trigger on tblBattleHistory) so you have all data at hands availabe via simple select.

Upvotes: 0

Related Questions