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.

Greg · Accepted Answer · 2009-11-07 23:36:21Z

8

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

answered Nov 7, 2009 at 23:36

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eglasius Over a year ago

+1, re @IronGoofy the answer is to get the OP on the right track, u don't need to code it for him ;)

Gabriel Magana Over a year ago

Lol, leave it to programmers to complicate something. There was no mention of ties at all in the OP, so why ask it? You may as well as if night-time games count.

OMG Ponies Over a year ago

@gmagana: Attention to detail and foresight is an essential skill

James Simpson Over a year ago

Great solution, I was thinking way too complicated trying to have a subquery. Also, I do have ties, but it can still work with ties if I just add "WHERE outcome < '2'" since outcome is set to 2 if it is a tie, and I don't need to know how many ties.

Thorsten · Accepted Answer · 2009-11-08 00:03:18Z

4

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.

edited Nov 8, 2009 at 0:03

answered Nov 7, 2009 at 23:55

Thorsten

13.2k18 gold badges66 silver badges79 bronze badges

Comments

Bobby · Accepted Answer · 2013-01-31 11:51:04Z

1

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.

edited Jan 31, 2013 at 11:51

answered Nov 7, 2009 at 23:59

Bobby

11.6k5 gold badges48 silver badges70 bronze badges

Comments

COTOHA · Accepted Answer · 2009-11-07 23:36:12Z

0

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.

answered Nov 7, 2009 at 23:36

COTOHA

2431 gold badge2 silver badges6 bronze badges

3 Comments

Joe Phillips Over a year ago

That would be considered redundant and de-normalization. Don't denormalize unless you have performance problems!

Gabriel Magana Over a year ago

If you want to simplify this via a "simple" SELECT then set up a view and do your simple select off that... No need to f**k up the DB design just to have simple queries. You DO know what a view is, right?

COTOHA Over a year ago

actually this is neither redundancy nor denormalization. this is just straight forward domain design. there is a user and user has win/loose ratio. this is absolutely not about performance. --- as for the view - the view you propose is a direct result of dumb domain models' design (no need to talk that these models are mapped to dumb DB structure) imo, of course :)

Trav L · Accepted Answer · 2009-11-07 23:43:55Z

0

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.

answered Nov 7, 2009 at 23:43

Trav L

15.3k6 gold badges34 silver badges39 bronze badges

Collectives™ on Stack Overflow

Select 2 COUNT()'s from MySQL query

5 Answers 5

4 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related