For example, i have a table with the data:
Screenshot
This table named “table“.
I have the SQL query:
select kind, count(kind) from table where region = 'eng' group by kind
And I get the result:
Question: how do I write a query that would return all the values that are in the kind field (or any other field that can be in group by)? Even if this value is 0. For the example above, the desired result is
It is mandatory to use group by in the query.
I use a postgresql 10.
Advertisement
Answer
Using a conditional aggregation
select kind, count(case region when 'eng' then kind end) cnt from table group by kind