CODEWITHSUNDEEP
sqlpostgresql
Korr Iamnot
Korr Iamnot

Reputation: 309

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

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 query (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 display like this

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

How could I query on this case? I use PostgreSQL.

Upvotes: 4

Views: 10137

Answers (3)

Bohemian
Bohemian

Reputation: 425448

You need a separate table with all pav_type values in it, let's name it pav_type_ref. Then outer join from it.

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

Upvotes: 0

mu is too short
mu is too short

Reputation: 434985

If your pav_type values are sequential, then you could use generate_series in place 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 yields:

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

This:

generate_series(0,3) as p(pav_type)

essentially generates an inlined table with a single column called pav_type and four rows: 0, 1, 2, 3. And you need to have the s.yes = 1 in the join condition (rather than the WHERE) because you want the yes = 0 values to be in the pre-grouped result set; if s.yes = 1 is in the WHERE clause then the yes = 0 rows won't be counted regardless of what the join condition is.

If your pav_types do not nicely fit with generate_series (i.e. not sequential or step-sequential) and you only have a small number of them, you could join to a 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 you get all the parentheses in the right places of course.

If you do have the pav_types in a separate table then do a LEFT OUTER JOIN to that table instead of using the generate_series. If your pav_types are not sequential and you have too many to sensibly put in a VALUES expression, then build a table to hold the valid pav_type values and LEFT OUTER JOIN to that.

Upvotes: 4

Jeff Wu
Jeff Wu

Reputation: 2578

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

Upvotes: 0

Related Questions