Output of non-existent values when grouping in sql-CodePudding

For example, i have a table with the data: 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.

CodePudding user response：

Using a conditional aggregation

select
    kind,
    count(case region when 'eng' then kind end) cnt
from table
group by kind

CodePudding user response：

select
 t1.kind,
 coalesce(t2.total, 0) total
from 
(
 select distinct kind from table
) t1
left join 
(
 select
  kind,
  count(kind) total
 from table 
 where region = 'eng'
 group by kind
)t2
on t1.kind = t2.kind

db fiddle