CODEWITHSUNDEEP
postgresqlcountlogical-operators
fadabi Nawi
fadabi Nawi

Reputation: 63

PostgreSQL count(boolean expression)

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

Upvotes: 3

Views: 691

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

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.

Upvotes: 4

Related Questions