Counting instances of value in postgres query

Question

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?

Gordon Linoff · Accepted Answer · 2019-11-06 12:45:46Z

2

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;

answered Nov 6, 2019 at 12:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-11-06 11:03:28Z

2

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

edited Nov 6, 2019 at 11:03

answered Nov 6, 2019 at 9:17

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

Gaspode Over a year ago

I fear one of us has misunderstood but I'm not sure which one :) I'd like the results to contain, per groupname, the number of rows where fatal == 'y'

Gaspode Over a year ago

Well, it did work but wasn't the solution I went for as the other one was, IMHO, cleaner.

Collectives™ on Stack Overflow

Counting instances of value in postgres query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related