sql - validar - Devuelve valor cero cuando no hay datos de fila

reemplazar el valor null de un campo en una consulta mysql

Question

Mi pregunta se ha abordado anteriormente, pero parece que no puedo aplicar ninguna solución a mi consulta para que funcione. Apreciaría mucho alguna orientación.

Mi consulta actual a continuación devuelve este conjunto de datos:

 | Age | Count |
0-1 day 300
2-3 days 6000
3-4 days 100 

SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day''
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days''
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days''
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days''
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed''
END) AS Age,
COUNT( * ) AS "Count"
FROM table_1
WHERE id IN (1,2,3)
GROUP BY (CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day''
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days''
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days''
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days''
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed''
END)
ORDER BY (CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day''
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN ''1-2 days''
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN ''2-3 days''
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN ''3-4 days''
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN ''Closed''
END)

Sin embargo, me gustaría que muestre las filas cero / nulas como ceros como este:

 | Age | Count |
0-1 day 300
1-2 days 0
2-3 days 6000
3-4 days 100
Closed 0

He leído todo tipo de los últimos días re: NVL, COALESCE, FULL / LEFT / RIGHT OUTER JOIN, IZQUIERDA / DERECHA SE UNE, UNIÓN TODO etc. ninguno de los cuales tenía declaraciones CASE e intenté solucionarlo, PERO! Debes saber cuándo parar y pedir indicaciones.

Answer 1

Primero, vuelve a escribir tu consulta. Utilice las vistas o la expresión de tabla común para evitar repetirse tres veces para sus cláusulas SELECT , GROUP BY , ORDER BY . Su consulta se convierte en:

WITH data AS (
SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day''
WHEN time_dtm > SYSDATE -2 AND
time_dtm < SYSDATE -1 THEN ''1-2 days''
WHEN time_dtm > SYSDATE -3 AND
time_dtm < SYSDATE -2 THEN ''2-3 days''
WHEN time_dtm > SYSDATE -4 AND
time_dtm < SYSDATE -3 THEN ''3-4 days''
WHEN time_dtm > SYSDATE -5 AND
time_dtm < SYSDATE -4 THEN ''Closed''
END) AS Age
FROM table_1
WHERE id IN (1,2,3)
)
SELECT Age, COUNT(*)
FROM data
GROUP BY Age
ORDER BY Age

Luego, para asegurarse de que cualquiera de sus grupos deseados esté disponible en el resultado, tiene muchas opciones.

Podría usar UNION ALL :

WITH data AS (
SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN ''0-1 day''
WHEN time_dtm > SYSDATE -2 AND
time_dtm < SYSDATE -1 THEN ''1-2 days''
WHEN time_dtm > SYSDATE -3 AND
time_dtm < SYSDATE -2 THEN ''2-3 days''
WHEN time_dtm > SYSDATE -4 AND
time_dtm < SYSDATE -3 THEN ''3-4 days''
WHEN time_dtm > SYSDATE -5 AND
time_dtm < SYSDATE -4 THEN ''Closed''
END) AS Age
FROM table_1
WHERE id IN (1,2,3)
-- The below will add one record for every desired Age group
UNION ALL
SELECT ''0-1 day'' FROM DUAL UNION ALL
SELECT ''1-2 days'' FROM DUAL UNION ALL
SELECT ''2-3 days'' FROM DUAL UNION ALL
SELECT ''3-4 days'' FROM DUAL UNION ALL
SELECT ''Closed'' FROM DUAL
)
SELECT Age, COUNT(*) - 1 -- Subtract the extra record again
FROM data
GROUP BY Age
ORDER BY Age

Una solución completamente diferente implicaría LEFT OUTER JOINs :

-- Groups is a dynamic table that contains the date ranges and their "Age" label
WITH groups AS (
SELECT SYSDATE -1 lower, SYSDATE upper, ''0-1 day'' Age FROM DUAL UNION ALL
SELECT SYSDATE -2 , SYSDATE -1 , ''1-2 days'' FROM DUAL UNION ALL
SELECT SYSDATE -3 , SYSDATE -2 , ''2-3 days'' FROM DUAL UNION ALL
SELECT SYSDATE -4 , SYSDATE -3 , ''3-4 days'' FROM DUAL UNION ALL
SELECT SYSDATE -5 , SYSDATE -4 , ''Closed'' FROM DUAL
)
SELECT g.Age, NVL(SUM(t.counter), 0)
FROM groups g
-- LEFT OUTER JOINing "table_1" to "groups" will ensure that every group
-- appears at least once in the result
LEFT OUTER JOIN (
SELECT 1 counter, t.* FROM table_1 t WHERE t.id IN (1,2,3)
) t
ON t.time_dtm >= g.lower
AND t.time_dtm < g.upper
GROUP BY g.Age
ORDER BY g.Age

En el segundo ejemplo, también podría hacerlo sin un CTE y usar un SELECT anidado para la tabla de groups . Es fácil ver cómo el segundo ejemplo es más simple de evolucionar en el futuro, en caso de que cambien sus requisitos.