nativas - Servidor SQL equivalente de una función agregada COUNTIF

función de agregación sql server

Question

Estoy construyendo una consulta con una cláusula GROUP BY que necesita la capacidad de contar registros basados ​​solo en una determinada condición (por ejemplo, contar solo registros donde un cierto valor de columna es 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

La línea COUNTIF() obviamente falla ya que no hay una función SQL nativa llamada COUNTIF , pero la idea aquí es determinar el porcentaje de todas las filas que tienen el valor ''1'' para MyColumn.

¿Alguna idea sobre cómo implementar esto correctamente en un entorno MS SQL 2005?

Answer 1

¿Por qué no me gusta esto?

SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1

Answer 2

Agregando a la respuesta de Josh,

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

Funcionó bien para mí (en SQL Server 2012) sin cambiar el ''recuento'' a ''suma'' y la misma lógica es portátil para otros ''agregados condicionales''. Por ejemplo, sumando según una condición:

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

Answer 3

No es específico del producto, pero el estándar SQL proporciona

SELECT COUNT() FILTER WHERE <condition-1>, COUNT() FILTER WHERE <condition-2>, ... FROM ...

para este propósito. O algo que se parece mucho a él, no lo sé desde lo alto de mi sombrero.

Y, por supuesto, los proveedores preferirán seguir con sus soluciones patentadas.

Answer 4

Puede usar una SUM (¡NO COUNT !) Combinada con una declaración CASE , como esta:

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

Nota: en mi propia prueba, los NULL no fueron un problema, aunque esto puede depender del entorno. Podría manejar valores nulos tales como:

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

Answer 5

Qué tal si

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

Más corto que CASE :)

Funciona porque COUNT() no cuenta valores nulos, y IF / CASE devuelve nulo cuando la condición no se cumple y no hay ELSE .

Creo que es mejor que usar SUM() .

Answer 6

Tuve que usar COUNTIF () en mi caso como parte de mis columnas SELECT Y para imitar un% de la cantidad de veces que cada elemento apareció en mis resultados.

Entonces usé esto ...

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

Por supuesto, tendrá que formatear el resultado de acuerdo con sus requisitos de visualización.

Answer 7

Usualmente hago lo que Josh me recomendó, pero hice una lluvia de ideas y probé una alternativa ligeramente divertida que me apetecía compartir.

Puede aprovechar el hecho de que COUNT (ColumnName) no cuenta NULL y usa algo como esto:

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

NULLIF - devuelve NULL si los dos valores pasados ​​son los mismos.

Ventaja: Expresa su intención de COUNT filas en lugar de tener la notación SUM (). Desventaja: No está tan claro cómo está funcionando (la "magia" suele ser mala).

Answer 8

Yo usaría esta sintaxis. Obtiene lo mismo que las sugerencias de Josh y Chris, pero con la ventaja de que es compatible con ANSI y no está vinculado a un proveedor de base de datos en particular.

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