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.
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.
generate_series()! Thanks for posting :)
generate_series the same way you just did :)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
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
