PostgreSQL - How to display count = 0 for this query?

Question

shelter_inventory (table)

gid | pav_type | yes 1 | 1 | 1 2 | 1 | 1 3 | 0 | 1 4 | 2 | 1 5 | 2 | 0 

this is the current request ( does not display count = 0 )

 SELECT pav_type, count(*) FROM shelter_inventory WHERE yes = 1 GROUP BY pav_type ORDER BY pav_type 

and I want the result to be displayed as follows

 pav_type | count(*) 0 | 1 1 | 2 2 | 1 3 | 0 

How can I request this case? I am using PostgreSQL.

Answer 1

If your pav_type values ​​are sequential, you can use generate_series instead of an external table:

 select p.pav_type, count(yes) from generate_series(0,3) as p(pav_type) left outer join shelter_inventory s on p.pav_type = s.pav_type and s.yes = 1 group by p.pav_type order by p.pav_type 

This gives:

  pav_type | count ----------+------- 0 | 1 1 | 2 2 | 1 3 | 0 

It:

 generate_series(0,3) as p(pav_type) 

essentially creates an inline table with one column named pav_type and four rows: 0, 1, 2, 3. And you need to have s.yes = 1 in the join condition (and not in WHERE), because you want the values yes = 0 which should be in a pre-grouped result set; if s.yes = 1 is in the WHERE clause, then yes = 0 lines will not be taken into account regardless of the connection conditions.

If your pav_types not suitable for generate_series (i.e. not sequential or phased), and you only have a small number of them, you can join the VALUES expression :

 select p.pav_type, count(yes) from (values (0), (1), (2), (3)) as p(pav_type) left outer join shelter_inventory s on p.pav_type = s.pav_type and s.yes = 1 group by p.pav_type order by p.pav_type 

You need to make sure that you, of course, find all brackets in the right places.

If you have pav_types in a separate table, then use the LEFT OUTER JOIN for this table instead of using generate_series . If your pav_types not sequential, and you have too much to reasonably enter a VALUES expression, then create a table to store the valid pav_type and LEFT OUTER JOIN values.

Answer 2

You need a separate table with all pav_type values, let's call it pav_type_ref. Then the outer connection is from him.

 create table pav_type_ref (pav_type int); insert into pav_type_ref values (1), (2), (3); SELECT r.pav_type, sum(case when i.pav_type is null then 0 else 1 end) FROM pav_type_ref r left join shelter_inventory i on i.pav_type = r.pav_type WHERE yes = 1 GROUP BY 1 ORDER BY 1 

Answer 3

Use SUM(CASE WHEN yes=1 THEN 1 ELSE 0 END) , you can also enable GROUP BY pav_type HAVING SUM(CASE WHEN yes=1 THEN 1 ELSE 0 END) = 0