Equivalente do Sql Server de uma function agregada COUNTIF

Eu estou construindo uma consulta com uma cláusula GROUP BY que precisa a capacidade de contar registros com base apenas em uma determinada condição (por exemplo, contar apenas registros em que um determinado valor de coluna é igual a 1).

 SELECT UID, COUNT(UID) AS TotalRecords, SUM(ContractDollars) AS ContractDollars, (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1 FROM dbo.AD_CurrentView GROUP BY UID HAVING SUM(ContractDollars) >= 500000 

A linha COUNTIF() obviamente falha, pois não existe uma function SQL nativa chamada COUNTIF , mas a idéia aqui é determinar a porcentagem de todas as linhas que possuem o valor ‘1’ para MyColumn.

Alguma idéia sobre como implementar isso corretamente em um ambiente MS SQL 2005?

Você poderia usar uma SUM (não COUNT !) Combinada com uma instrução CASE , assim:

 SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END) FROM AD_CurrentView 

Nota: no meu próprio teste, os NULL não foram um problema, embora isso possa ser dependente do ambiente. Você poderia manipular nulos como:

 SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END) FROM AD_CurrentView 

Eu costumo fazer o que o Josh recomendou, mas fiz um brainstorming e testei uma alternativa levemente piegas que eu queria compartilhar.

Você pode aproveitar o fato de que COUNT (ColumnName) não conta NULLs e usa algo assim:

 SELECT COUNT(NULLIF(0, myColumn)) FROM AD_CurrentView 

NULLIF – retorna NULL se os dois valores passados ​​forem os mesmos.

Vantagem: expressa sua intenção para COUNT linhas em vez de ter a notação SUM (). Desvantagem: Não está claro como está funcionando (“mágica” geralmente é ruim).

Eu usaria essa syntax. Atinge o mesmo que as sugestões de Josh e Chris, mas com a vantagem é compatível com ANSI e não vinculado a um determinado fornecedor de database.

 select count(case when myColumn = 1 then 1 else null end) from AD_CurrentView 

Adicionando a resposta de Josh,

 SELECT COUNT(CASE WHEN myColumn=1 THEN AD_CurrentView.PrimaryKeyColumn ELSE NULL END) FROM AD_CurrentView 

Trabalhei bem para mim (no SQL Server 2012) sem alterar a ‘contagem’ para ‘sum’ e a mesma lógica é portável para outros ‘agregados condicionais’. Por exemplo, sumndo com base em uma condição:

 SELECT SUM(CASE WHEN myColumn=1 THEN AD_CurrentView.NumberColumn ELSE 0 END) FROM AD_CurrentView 

Não é específico do produto, mas o padrão SQL fornece

SELECT COUNT() FILTER WHERE , COUNT() FILTER WHERE , ... FROM ...

para este fim. Ou algo que se assemelha a isso, eu não sei o topo do meu chapéu.

E, claro, os fornecedores preferirão ficar com suas soluções proprietárias.

Por que não assim?

 SELECT count(1) FROM AD_CurrentView WHERE myColumn=1 

E se

 SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt FROM table GROUP BY table 

Mais curto que o CASE 🙂

Funciona porque COUNT() não conta valores nulos e IF / CASE retorna null quando a condição não é atendida e não há ELSE .

Eu acho que é melhor do que usar SUM() .

Eu tive que usar COUNTIF () no meu caso como parte de minhas colunas SELECT e para imitar um% do número de vezes que cada item apareceu nos meus resultados.

Então eu usei isso …

 SELECT COL1, COL2, ... ETC (1 / SELECT a.vcount FROM (SELECT vm2.visit_id, count(*) AS vcount FROM dbo.visitmanifests AS vm2 WHERE vm2.inactive = 0 AND vm2.visit_id = vm.Visit_ID GROUP BY vm2.visit_id) AS a)) AS [No of Visits], COL xyz FROM etc etc 

Claro que você precisará formatar o resultado de acordo com seus requisitos de exibição.