CODEWITHSUNDEEP
sqlpostgresql
SUBHAS PATIL
SUBHAS PATIL

Reputation: 152

Postgres SQL query to find the count of a value from a column

Lets say I have a postgres table with the following data:

hostname | status
---------+--------
a1       | C
a1       | C
a1       | NC
a2       | NC
a2       | NC
a2       | C

I want to display the count of status for C and NC for each hostname, like this:

hostname | C total| NC total | Total (C+NC) | C %
---------+--------+----------+--------------+-----
a1       | 2      | 1        | 3            |66.66
a2       | 1      | 2        | 3            |33.33

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use conditional aggregation:

select hostname,
       count(*) filter (where status = 'C') as num_c,
       count(*) filter (where status = 'NC') as num_nc
from t
group by hostname;

For the edit made AFTER I answered, you just follow the same idea:

select hostname,
       count(*) filter (where status = 'C') as num_c,
       count(*) filter (where status = 'NC') as num_nc,
       count(*),
       avg( (status = 'C')::int ) as c_ratio
from t
group by hostname;

Upvotes: 1

Related Questions