Conditional SQL count

by

In Postgres 9.4 or later, use the aggregate FILTER option. Typically cleanest and fastest:

SELECT category
     , count(*) FILTER (WHERE question1 = 0) AS zero
     , count(*) FILTER (WHERE question1 = 1) AS one
     , count(*) FILTER (WHERE question1 = 2) AS two
FROM   reviews
GROUP  BY 1;

Details for the FILTER clause:

If you want it short:

SELECT category
     , count(question1 = 0 OR NULL) AS zero
     , count(question1 = 1 OR NULL) AS one
     , count(question1 = 2 OR NULL) AS two
FROM   reviews
GROUP  BY 1;

More syntax variants:

Proper crosstab query

crosstab() yields the best performance and is shorter for long lists of options:

SELECT * FROM crosstab(
     'SELECT category, question1, count(*) AS ct
      FROM   reviews
      GROUP  BY 1, 2
      ORDER  BY 1, 2'
   , 'VALUES (0), (1), (2)'
   ) AS ct (category text, zero int, one int, two int);

Detailed explanation:

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  3. Aggregate columns with additional (distinct) filters
  4. How to use count and group by at the same select statement
  5. Fast way to discover the row count of a table in PostgreSQL
  6. Query with LEFT JOIN not returning rows for count of 0
  7. SQL query for finding records where count > 1
  8. GROUP BY + CASE statement
  9. Compute percents from SUM() in the same SELECT sql query
  10. SQL to Entity Framework Count Group-By
  11. PostgreSQL Where count condition
  12. Get values from first and last row per group
  13. How do I speed up counting rows in a PostgreSQL table?
  14. Run a query with a LIMIT/OFFSET and also get the total number of rows
  15. Query for count of distinct values in a rolling date range
  16. Difference between LIKE and ~ in Postgres
  17. Pivot on Multiple Columns using Tablefunc
  18. Update multiple rows in same query using PostgreSQL
  19. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  20. Selecting data into a Postgres array
  21. Get the distinct sum of a joined table column
  22. Postgres Error: More than one row returned by a subquery used as an expression
  23. Operator does not exist: json = json
  24. Function with SQL query has no destination for result data
  25. Postgres INTERVAL using value from table
  26. Crosstab with a large or undefined number of categories
  27. Truncate timestamp to arbitrary intervals
  28. Conditional lead/lag function PostgreSQL?
  29. Postgres and Indexes on Foreign Keys and Primary Keys
  30. Select random row from a PostgreSQL table with weighted row probabilities

Leave a Comment