Skip to content
Advertisement

Output of non-existent values when grouping in sql

For example, i have a table with the data:
Screenshot
enter image description here

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:
enter image description here

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
enter image description here

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
Advertisement