What is the best way in Postgresql, I want to count how many count with value = 1 in field A and how many count with value = 0 in same field A.
something like this:
select
count (field1 = value1) as filed1_ok,
count (field1 = value2) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
Use count(condition or null)
select
count(field1 = 1 or null) as filed1_ok,
count(field1 = 0 or null) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
true or null evaluates to true. false or null evaluates to null. Since count does not count nulls that is exactly what you want.
In other SQL dialects and also in Postgresql it can be done using case
select
coalesce(sum(case field1 when 1 then 1 end), 0) as filed1_ok,
coalesce(sum(case field1 when 0 then 1 end), 0) as filed1_bad,
extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth
I consider it verbose and opaque compared to the count(condition or null) Postgresql option.