sql - vacio - Suma columnas con valores nulos en oracle.
sumar resultados de una consulta sql (8)
Código:
select type, craft, sum(coalesce( regular + overtime, regular, overtime)) as total_hours
from hours_t
group by type, craft
order by type, craft
Quiero sumar dos números, pero cuando uno de esos números es nulo, el resultado es nulo. Hay alguna forma de evitar esto. Simplemente podría hacerlo en el código, pero preferiría hacerlo en la consulta. Esta es una base de datos de Oracle.
La estructura de la mesa
hours_t
type craft regular overtime
A 1 5 0
A 1 3 1
B 2 9 <null>
B 1 4 4
La consulta
select type, craft, sum(regular + overtime) as total_hours
from hours_t
group by type, craft
order by type, craft
Los resultados no deseados.
type craft total_hours
A 1 9
B 1 8
B 2 <null>
Los resultados buscados
type craft total_hours
A 1 9
B 1 8
B 2 9
En algunos casos, también se requiere nvl (sum (column_name), 0). Es posible que desee considerar sus escenarios.
Por ejemplo, estoy tratando de obtener la suma de una columna particular, de una tabla particular basada en ciertas condiciones. Basado en las condiciones,
- una o más filas existen en la tabla. En este caso quiero la suma.
- Las filas no existen. En este caso quiero 0.
Si usa sum (nvl (column_name, 0)) aquí, le daría un valor nulo. Lo que podrías querer es nvl (sum (column_name), 0).
Esto puede ser necesario, especialmente cuando está pasando este resultado a, digamos, java, tener el tipo de datos como número allí porque entonces esto no requerirá un manejo nulo especial.
La respuesta mejor calificada con NVL es totalmente válida. Si tiene algún interés en hacer que su código SQL sea más portátil, es posible que desee utilizar CASE, que es compatible con la misma sintaxis tanto en Oracle como en SQL Server:
select
type,craft,
SUM(
case when regular is null
then 0
else regular
end
+
case when overtime is null
then 0
else overtime
end
) as total_hours
from
hours_t
group by
type
,craft
order by
type
,craft
Las otras respuestas con respecto al uso de nvl () son correctas, sin embargo, ninguna parece abordar un punto más destacado:
¿Debería incluso tener valores nulos en esta columna?
¿Tienen un significado distinto de 0?
Esto parece ser un caso en el que debería tener un 0 NULL DEFAULT DEFAULT en la ecolumna
NVL (valor, predeterminado) es la función que está buscando.
select type, craft, sum(NVL(regular, 0) + NVL(overtime, 0) ) as total_hours
from hours_t
group by type, craft
order by type, craft
Oracle tiene 5 funciones relacionadas con NULL:
- NVL
- NVL2
- JUNTARSE
- NULLIF
- LNNVL
NVL :
NVL(expr1, expr2)
NVL le permite reemplazar nulo (devuelto como un espacio en blanco) con una cadena en los resultados de una consulta. Si expr1 es nulo, entonces NVL devuelve expr2. Si expr1 no es nulo, entonces NVL devuelve expr1.
NVL2 :
NVL2(expr1, expr2, expr3)
NVL2 le permite determinar el valor devuelto por una consulta en función de si una expresión especificada es nula o no nula. Si expr1 no es nulo, entonces NVL2 devuelve expr2. Si expr1 es nulo, entonces NVL2 devuelve expr3.
COALESCE(expr1, expr2, ...)
COALESCE devuelve el primer expr no nulo en la lista de expresiones. Al menos un expr no debe ser el NULL literal. Si todas las apariciones de expr se evalúan como nulas, la función devuelve un valor nulo.
NULLIF(expr1, expr2)
NULLIF compara expr1 y expr2. Si son iguales, entonces la función devuelve null. Si no son iguales, entonces la función devuelve expr1. No puede especificar el literal NULL para expr1.
LNNVL(condition)
LNNVL proporciona una forma concisa de evaluar una condición cuando uno o ambos operandos de la condición pueden ser nulos.
Más información sobre las funciones SQL de Oracle
Necesitas usar la función NVL, por ejemplo
SUM (NVL (regular, 0) + NVL (horas extras, 0))
select type, craft, sum(NVL(regular, 0) + NVL(overtime, 0)) as total_hours
from hours_t
group by type, craft
order by type, craft
select type, craft, sum(nvl(regular,0) + nvl(overtime,0)) as total_hours
from hours_t
group by type, craft
order by type, craft