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

Question

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

Gordon Linoff · Accepted Answer · 2020-11-24 23:17:48Z

1

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;

edited Nov 24, 2020 at 23:17

answered Nov 24, 2020 at 17:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SUBHAS PATIL Over a year ago

Thanks. Can I get the C% as well like this:

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related