sql - 计算 postgres 查询中的值实例

Question

一些 SQL 新手问题..

如果我有一个如下所示的表格:

host    fault                                             fatal groupnameHost A  Data smells iffy                                  n     researchHost B  Flanklecrumpet needs a cuddle                     y     productionHost A  RAM loves EWE                                     n     researchHost 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     

我可以在同一个查询中显示致命条目的数量吗？

Answer 1

我会使用聚合，但在 Postgres 中会这样表述:

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