Aggregate columns with additional (distinct) filters

by

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:

More Related Contents:

  1. PostgreSQL: running count of rows for a query ‘by minute’
  2. Two SQL LEFT JOINS produce incorrect result
  3. Create a pivot table with PostgreSQL
  4. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  5. Return multiple columns of the same row as JSON array of objects
  6. How to include “zero” / “0” results in COUNT aggregate?
  7. GROUP BY + CASE statement
  8. Best way to count rows by arbitrary time intervals
  9. Selecting data into a Postgres array
  10. Compute percents from SUM() in the same SELECT sql query
  11. Get the distinct sum of a joined table column
  12. Aggregate a single column in query with many columns
  13. Multiple array_agg() calls in a single query
  14. Postgres window function and group by exception
  15. Conditional SQL count
  16. Count cumulative total in Postgresql
  17. Truncate timestamp to arbitrary intervals
  18. Best way to select random rows PostgreSQL
  19. What is the maximum number of columns in a PostgreSQL select query
  20. Humanized or natural number sorting of mixed word-and-number strings
  21. postgres column “X” does not exist
  22. PostgreSQL: encoding problems on Windows when using psql command line utility
  23. The SQL OVER() clause – when and why is it useful?
  24. Aggregating (x,y) coordinate point clouds in PostgreSQL
  25. Does Postgres support nested or autonomous transactions?
  26. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  27. Update a column of a table with a column of another table in PostgreSQL
  28. Check if NULL exists in Postgres array
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment