Conditional SQL count

Use the aggregate FILTER option in Postgres 9.4 or later:

SELECT category     , count(*) FILTER (WHERE question1 = 0) AS zero     , count(*) FILTER (WHERE question1 = 1) AS one     , count(*) FILTER (WHERE question1 = 2) AS twoFROM   reviewsGROUP  BY 1;

Details for the FILTER clause:

• Aggregate columns with additional (distinct) filters

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 twoFROM   reviewsGROUP  BY 1;

Overview over possible variants:

• For absolute performance, is SUM faster or COUNT?

Proper crosstab query

crosstab() yields the best performance and is shorter for longer 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:

• PostgreSQL Crosstab Query

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

SELECT    category,    question1,    count(*)FROM reviewsGROUP BY category, question1

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

Other option is to use one JSON column for all grouping results. This will result in something like:

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

and so on.

The query for this option you can build from the previous one with json_build_object and json_agg. The best thing for this option - you do not need to know number of possible question1 values ahead of time.