SQL count if columns

Question

What is the best way to create columns that count the number of occurrences of data in a table? The table should be grouped by one column.

I have seen

SELECT sum(CASE WHEN question1 = 0 THEN 1 ELSE 0 END) AS ZERO, sum(CASE WHEN question1 = 1 THEN 1 ELSE 0 END) AS ONE, sum(CASE WHEN question1 = 2 THEN 1 ELSE 0 END) AS TWO, category FROM reviews GROUP BY category 

where question1 can be 0, 1, or 2.

I also saw a version using count(CASE WHEN question1 = 0 THEN 1)

However, this becomes more cumbersome as the number of possible values ​​for question 1 is increasing. Is there a convenient way to write this query, possibly optimizing performance?

PS. My database is PostgreSQL

Answer 1

Postgres 9.4 introduced a new, cleaner FILTER aggregate:

 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 new FILTER offer:

• How can I simplify this query of game statistics?

If you want briefly:

 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; 

Overview of options:

• For absolute performance, is SUM faster or COUNT?

Correct crosstab query

crosstab() gives better performance and shorter for longer option lists:

 SELECT * FROM crosstab( 'SELECT category, question1, count(*)::int 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:

• PostgreSQL crosstab query

Answer 2

The "best" way (for me) is to write a query like:

 SELECT category, question1, count(*) FROM reviews GROUP BY category, question1 

Then I use this data to draw a table in the application logic.

Another option is to use a single JSON column for all grouping results. This will result in something like:

 category1 | {"zero": 1, "one": 3, "two": 5} category2 | {"one": 7, "two": 4} 

etc.

A query for this option, which you can build from the previous one using json_build_object and json_agg . Best for this option - you do not need to know in advance the number of possible question1 values.