Filter as count on SQL SERVER

Question

Helo!

How I make the following syntax of postgresql in SQL server without create subquery

PGSQL:

SELECT 
    COUNT(*) AS "QUANTIDADE TOTAL",
    COUNT(*) FILTER(WHERE SEXO = 'Masculino') AS "MASCULINO"
FROM FUNCIONARIOS;

I tried but got an error:
Message 156, Level 15, State 1, Line 4

Incorrect syntax next to 'WHERE' keyword.

score 1 · Answer 1 · answered Oct 28 '22 at 21:54

1

Conditional aggregation would work...

SELECT 
    COUNT(*) AS "QUANTIDADE TOTAL",
    SUM(case when SEXO = 'Masculino' then 1 end) AS "MASCULINO"
FROM FUNCIONARIOS;

answered Oct 28 '22 at 21:54

Isolated

I think you'll need `ELSE 0` inside `SUM` (behavior is different with `COUNT`). – Stuck at 1337 Oct 28 '22 at 22:07
@RhythmWasaLurker Works with or without `ELSE`. It's a preference if you would rather return null vs 0 if no data matches. Based on the OP's query, I doubt that Masculino would not be found. – Isolated Oct 31 '22 at 13:47
I guess "works" depends if you actually want a count or if NULL is a reasonably expected substitution for 0. – Stuck at 1337 Oct 31 '22 at 13:59

score 1 · Accepted Answer · answered Oct 29 '22 at 06:03

1

Try SELECT COUNT(x) as [Quantudate Total], SUM (CASE WHEN SEXO = 'Masculino' THEN 1 ELSE 0 END) AS MASCULINO FROM FUNCIONARIOS

For better performance always better to use one specific field in the aggregate function than using a , eg use COUNT(id) than COUNT()

answered Oct 29 '22 at 06:03

Shah

Very thank you #Shah and #Isolated! With your help , I tried this and work very well, follow final code: SELECT TOP 1 COUNT(*) AS 'TOTAL', SUM((CASE WHEN SEX = 'W' THEN 1 WHEN SEX <> 'W' THEN 0 END)) AS 'MASCULINO' FROM [DATA_SCIENCE].[dbo].[LOSS_JOBS_COVID] – Ricardo Luiz Vieira Oct 30 '22 at 00:55

2 Answers2