sql-server - vacios - suma de una columna en sql server

Contar el número de valores NULL en una fila

Question

Quiero contar la cantidad de valores NULL en una fila.

Por ejemplo:

Name    Col1    Col2      Col3     Col4 | ansCol
abc     null   [email protected]    null     null | 3
bbc     null    null      null     null | 4

En este ejemplo, la respuesta es simple:

SELECT *,
(CASE WHEN Name IS NULL THEN 1 ELSE 0 END +
CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END +       
CASE WHEN Col2 IS NULL THEN 1 ELSE 0 END +       
CASE WHEN Col3 IS NULL THEN 1 ELSE 0 END +       
CASE WHEN Col4 IS NULL THEN 1 ELSE 0 END) as ansCol

La pregunta se vuelve más compleja cuando tenemos una tabla muy amplia, más de 10 columnas. Si intento utilizar el método que se muestra, aparece el siguiente error:

Msg 125, nivel 15, estado 4, línea 13 Las expresiones de caso solo se pueden anidar al nivel 10.

¿Hay alguna forma de superar esto?

Answer 1

El código en su pregunta no tiene ningún caso anidado , por lo que debería poder encadenar más de 10 con una suma muy buena.

Pero algunas otras posibilidades son ...

Si todas las columnas tienen el mismo tipo de datos, puede usar ( Demo )

SELECT *,
       ansCol = (SELECT COUNT(*) - COUNT(C) FROM (VALUES(c1),(c2),(c3),(c4)) V(C))
FROM   t1 

O bien, otra alternativa que no dependa de que tengan el mismo tipo de datos.

;WITH XMLNAMESPACES(''http://www.w3.org/2001/XMLSchema-instance'' as ns)
SELECT *, 
        ansCol = (SELECT x.* FOR XML PATH(''row''), 
                    ELEMENTS XSINIL, TYPE).value(''count(/row/*[@ns:nil])'', ''int'') 
FROM   t1 x

Answer 2

Esto se siente como un truco, y quizás deba cambiar su diseño o reducir el número de columnas en su tabla, pero si debe hacer esto, una opción sería usar una subconsulta para sortear el límite anidado de 10 columnas:

SELECT t.*,
       t.firstBatch + t.secondBatch AS ansCol
FROM
(
    SELECT *,
        (CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END +  
         CASE WHEN Col2 IS NULL THEN 1 ELSE 0 END +       
         ...
         CASE WHEN Col10 IS NULL THEN 1 ELSE 0 END) AS firstBatch,
        (CASE WHEN Col11 IS NULL THEN 1 ELSE 0 END +  
         CASE WHEN Col12 IS NULL THEN 1 ELSE 0 END +       
         ...
         CASE WHEN Col20 IS NULL THEN 1 ELSE 0 END) AS secondBatch
    FROM yourTable
) t

Answer 3

Además de lo anterior, un enfoque potencial sería usar la funcionalidad UNPIVOT y UNPIVOT .

Primero crearemos la tabla e insertaremos los datos según la pregunta:

/* Create table */
CREATE TABLE so_40957006 (
    [name]  VARCHAR(24),
    [col1]  VARCHAR(24),
    [col2]  VARCHAR(24),
    [col3]  VARCHAR(24),
    [col4]  VARCHAR(24)
)

/* Insert data */
INSERT INTO so_40957006 VALUES(''abc'', null, ''[email protected]'', null, null)
INSERT INTO so_40957006 VALUES(''bbc'', null, null, null, null)

Lo primero que haremos será crear una tabla de identidad para que podamos identificar de forma única cada fila de datos e insertar los datos.

/* Create identity table */
CREATE TABLE so_40957006_id (
    [lid]   INT IDENTITY(1, 1),
    [name]  VARCHAR(24),
    [col1]  VARCHAR(24),
    [col2]  VARCHAR(24),
    [col3]  VARCHAR(24),
    [col4]  VARCHAR(24)
)

/* Insert data into table */
INSERT INTO so_40957006_id 
SELECT 
  ISNULL([name], 1) AS [name]
 ,ISNULL([col1], 1) AS [col1]
 ,ISNULL([col2], 1) AS [col2]
 ,ISNULL([col3], 1) AS [col3]
 ,ISNULL([col4], 1) AS [col4]
FROM so_40957006

Tenga en cuenta que en el proceso hemos convertido cualquiera de las columnas que son nulas a un valor de 1 mediante IsNull . La tabla ahora se ve a continuación.

lid name    col1    col2    col3    col4
1   abc     1       [email protected] 1       1
2   bbc     1       1       1       1

Con esto, ahora podemos usar PIVOT y UNPIVOT para verticalizar los datos. Por ejemplo, ejecutando la consulta a continuación

SELECT [lid], [cols], [val]
FROM
    (SELECT [lid], [name], [col1], [col2], [col3], [col4]
        FROM so_40957006_id) p
UNPIVOT
    (val FOR cols IN 
    ([name], [col1], [col2], [col3], [col4])
) AS unpvt

le proporciona la salida vertical de:

lid cols    val
1   name    abc
1   col1    1
1   col2    [email protected]
1   col3    1
1   col4    1
2   name    bbc
2   col1    1
2   col2    1
2   col3    1
2   col4    1

Como los valores que desea contar (es decir, los NULL s) tienen un valor de 1, puede ejecutar una instrucción GROUP BY + SUM :

SELECT [lid], SUM(cast([val] as integer)) as CountNulls
  FROM (
    SELECT [lid], [cols], [val]
    FROM
        (SELECT [lid], [name], [col1], [col2], [col3], [col4]
           FROM so_40957006_id) p
    UNPIVOT
     (val FOR cols IN 
       ([name], [col1], [col2], [col3], [col4])
    ) AS unpvt
) a 
WHERE ISNUMERIC([val]) = 1
GROUP BY [lid]

con la salida

lid CountNulls
1   3
2   4

Con la [lid] , puede JOIN esta salida a la tabla original para generar su conjunto de resultados.