CODEWITHSUNDEEP
mysqlsqlpostgresql
Judking
Judking

Reputation: 6381

How to do count on a field in SQL which allows count of zero on a specific group?

Here's some psudo-content from a table in database.

type  approved
A     true
A     true
B     true
C     false

What I intend to get for result is:

A 2
B 1
C 0

which calculates the number of each type that is approved. But if I execute SQL select count(approved) from table where approved = true, it will only show

A 2
B 1

What could I do to show the C 0, too? Many thanks!

Upvotes: 0

Views: 25

Answers (2)

econnormist
econnormist

Reputation: 80

Or instead of case you can use nullif:

select
    type,
    count(nullif(approved, false)) as true_count
from YourTable
group by type;

http://www.postgresql.org/docs/9.0/static/functions-conditional.html

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use conditional aggregation.

select type, sum(case when approved = 'true' then 1 else 0 end) as total 
from table
group by type

Upvotes: 1

Related Questions