CODEWITHSUNDEEP
sqlpostgresql
Gaspode
Gaspode

Reputation: 148

Counting instances of value in postgres query

Bit of an SQL newbie question..

If I have a table along the lines of the following :

host    fault                                             fatal groupname
Host A  Data smells iffy                                  n     research
Host B  Flanklecrumpet needs a cuddle                     y     production
Host A  RAM loves EWE                                     n     research
Host Z  One of the crossbeams gone askew on the treadle   y     research

.. and I want to get some stats, I can..

select count(distinct host) as hosts, count(host) as faults, group from tablename group by groupname

.. which gives me the number of faults and affected hosts per groupname.

hosts    faults    groupname
2        3         research
1        1         production

Can I, in the same query, show the number of fatal entries?

Upvotes: 1

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

I would use aggregation, but in Postgres would phrase this as:

select groupname, count(distinct host) as hosts,
       count(*) as num_faults,
       count(*) filter (where fatal = 'Y') as num_fatal
from t
group by groupname;

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use conditional aggregation

 select count(distinct host) as hosts,
 count(host) as faults,sum(case when fatal='y' then 1 else 0 end) as numberofenty,
 groupname from tablename group by groupname

Upvotes: 2

Related Questions