By ave


2014-11-25 20:39:27 8 Comments

This code works as expected, but I it's long and creepy.

select p.name, p.played, w.won, l.lost from

(select users.name, count(games.name) as played
from users
inner join games on games.player_1_id = users.id
where games.winner_id > 0
group by users.name
union
select users.name, count(games.name) as played
from users
inner join games on games.player_2_id = users.id
where games.winner_id > 0
group by users.name) as p

inner join

(select users.name, count(games.name) as won
from users
inner join games on games.player_1_id = users.id
where games.winner_id = users.id
group by users.name
union
select users.name, count(games.name) as won
from users
inner join games on games.player_2_id = users.id
where games.winner_id = users.id
group by users.name) as w on p.name = w.name

inner join

(select users.name, count(games.name) as lost
from users
inner join games on games.player_1_id = users.id
where games.winner_id != users.id
group by users.name
union
select users.name, count(games.name) as lost
from users
inner join games on games.player_2_id = users.id
where games.winner_id != users.id
group by users.name) as l on l.name = p.name

As you can see, it consists of 3 repetitive parts for retrieving:

  • player name and the amount of games they played
  • player name and the amount of games they won
  • player name and the amount of games they lost

And each of those also consists of 2 parts:

  • player name and the amount of games in which they participated as player_1
  • player name and the amount of games in which they participated as player_2

How could this be simplified?

The result looks like so:

           name            | played | won | lost 
---------------------------+--------+-----+------
 player_a                  |      5 |   2 |    3
 player_b                  |      3 |   2 |    1
 player_c                  |      2 |   1 |    1

3 comments

@Erwin Brandstetter 2014-11-26 04:12:24

The aggregate FILTER clause in Postgres 9.4 or newer is shorter and faster:

SELECT u.name
     , count(*) FILTER (WHERE g.winner_id  > 0)    AS played
     , count(*) FILTER (WHERE g.winner_id  = u.id) AS won
     , count(*) FILTER (WHERE g.winner_id <> u.id) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

In Postgres 9.3 (or any version) this is still shorter and faster than nested sub-selects or CASE expressions:

SELECT u.name
     , count(g.winner_id  > 0 OR NULL)    AS played
     , count(g.winner_id  = u.id OR NULL) AS won
     , count(g.winner_id <> u.id OR NULL) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

Details:

@Multisync 2014-11-25 20:49:06

select users.name, 
       count(case when games.winner_id > 0 
                  then games.name 
                  else null end) as played,
       count(case when games.winner_id = users.id 
                  then games.name 
                  else null end) as won,
       count(case when games.winner_id != users.id 
                  then games.name 
                  else null end) as lost
from users inner join games 
     on games.player_1_id = users.id or games.player_2_id = users.id
group by users.name;

@Joe Love 2014-11-25 21:11:38

and this will be completely trivial after 9.4 when aggregate filters are implemented. These types of "case" statements will be a thing of the past.

@Gordon Linoff 2014-11-25 20:46:17

This is a case where correlated subqueries may simplify the logic:

select u.*, (played - won) as lost
from (select u.*,
             (select count(*)
              from games g
              where g.player_1_id = u.id or g.player_2_id = u.id
             ) as played,
             (select count(*)
              from games g
              where g.winner_id = u.id
             ) as won
      from users u
     ) u;

This assumes that there are no ties.

Related Questions

Sponsored Content

41 Answered Questions

2 Answered Questions

[SOLVED] Using group by on multiple columns

2 Answered Questions

[SOLVED] How to write this query in laravel? Any Suggestions?

18 Answered Questions

3 Answered Questions

[SOLVED] GROUP BY to get several aggregates per row

  • 2018-02-28 15:30:33
  • Carlos Alves Jorge
  • 50 View
  • 1 Score
  • 3 Answer
  • Tags:   sql sql-server

4 Answered Questions

1 Answered Questions

[SOLVED] sqlalchemy.exc.ProgrammingError for flask

3 Answered Questions

[SOLVED] Select count of two columns while grouped by

  • 2015-02-07 12:48:47
  • Stella
  • 48 View
  • 0 Score
  • 3 Answer
  • Tags:   mysql sql

2 Answered Questions

SQL How to INNER JOIN multiple values with similar field names

  • 2013-04-23 16:31:37
  • October Eleven
  • 421 View
  • 0 Score
  • 2 Answer
  • Tags:   sql join

Sponsored Content