Select 2 COUNT()'s from MySQL query

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

This might also work:

SELECT username, SUM(IF(outcome = 1, 1, 0)) AS Wins, SUM(IF(outcome = 0, 1, 0)) AS Losses FROM tblBattleHistory

Bobby