如何用一个SQL查询得到不同的计数？

Question

select location,
       count(*) as tot_proj where project_status='live',
       sum(fte_count) as tot_emp where project_status='live' and fte_count != 'NULL'
from table_name 
group by location

select count(*) as tot_proj,
       "location" ,
       sum(fte_count::float) as ft
 from insights_view iv2 
 where project_status = 'Live' and fte_count != 'NULL'
 group by "location"

Answer 1

我认为您需要以下语法：

select location,
       count(*) filter(where project_status = 'live') as tot_proj ,
       sum(fte_count) filter(where project_status = 'live') as tot_emp 
from table_name 
group by location

这使用标准的filter子句聚合函数，Postgres支持这一功能。您不需要检查fte_count是否为null，因为sum()忽略null值（如果必须，则需要is not null而不是！='null')。

另一方面，如果您运行的是MySQL，而不是Postgres：

select location,
       sum(project_status = 'live') as tot_proj ,
       sum(case when project_status = 'live' then fte_count end) as tot_emp 
from table_name 
group by location