Question 114847 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    Using a conditional aggregation

    select
    kind,
    count(case region when 'eng' then kind end) cnt
from table
group by kind
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search