How to get different counts with one SQL query?

Question

I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query.

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

i want all this in one query...i tried this one

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"

but my tot_proj count is not matching...

Answer 1

I think that you want the following syntax:

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

This uses the standard filter clause to aggregate functions, which Postgres supports. You don't need to check if fte_count is null, since sum() ignores nullvalues (and if you had to, you would need is not null rather than != 'NULL').

On the other hand if you are running MySQL and not 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

Answer 2

I would move the 'live' comparison to a where clause. Then, if you really mean NULL and not 'NULL' you can just add up the values. So:

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

Note that 'NULL' is a string. It doesn't make sense to use sum() on a string, so I am guessing you really mean NULL the SQL keyword. If so, that value is ignored by sum() and most other aggregation functions.

Also, the above will filter out any locations that have no "live" projects. Because you propose a version with a where filter, I am guessing this is not an issue.

Answer 3

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