Postgres nested SQL query to count field

Question

I have a few tables:

users - id

users_matches - user_id, match_id, team

matches - id, winner

I'd like to count how many wins, losses, and ties a user has. team is an integer that could be 1 or 2. winner is also an integer, but it can be 1 (team 1 wins), 2 (team 2 wins), or 3 (tie).

I'm not sure how to mix a grouped count with a nested query in Postgres.

Postgres 9.4 I assume?

Erwin Brandstetter
– Erwin Brandstetter

2015-07-30 06:14:25 +00:00
Commented Jul 30, 2015 at 6:14 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 30, 2015 at 6:14

Community · Accepted Answer · 2017-05-23 12:07:55Z

3

Assuming referential integrity and Postgres 9.4:

SELECT *, total - wins - ties AS losses
FROM (
   SELECT count(*) AS total
        , count(*) FILTER (WHERE m.winner = um.team) AS wins
        , count(*) FILTER (WHERE m.winner = 3) AS ties
   FROM   users_matches um
   JOIN   matches m ON m.id = um.match_id
   WHERE  um.user_id = 123;  -- for one given user
) sub;

About the aggregate FILTER clause (introduced with Postgres 9.4):

How can I simplify this game statistics query?

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Jul 30, 2015 at 6:11

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Postgres nested SQL query to count field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related