CODEWITHSUNDEEP
sqlsql-serverconditional-aggregation
Ricardo Luiz Vieira
Ricardo Luiz Vieira

Reputation: 13

Filter as count on SQL SERVER

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.

Upvotes: -1

Views: 68

Answers (2)

Shah
Shah

Reputation: 104

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()

Upvotes: 1

Isolated
Isolated

Reputation: 6454

Conditional aggregation would work...

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

Upvotes: 0

Related Questions