Trabajando en un reporte mensual, debía separar por criterio el total generado en una sumatoria.
Normalmente para separarlo se utiliza la agrupación, ya tenemos la primera agrupación que sería por mes y la segunda seria por el criterio, como por ejemplo sumar el sueldo mensual de varios empleados pero diferenciando por el sueldo y bonos que tengan.
Vamos a crear una tabla de pagos:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE pagos ( id number PRIMARY KEY NOT NULL, id_empleado number NOT NULL, fecha date NOT NULL, tipo_pago VARCHAR2(10) NOT NULL, monto number(18,2) NOT NULL ); INSERT INTO pagos VALUES (1, 047, TO_DATE('2022-01-01','YYYY-MM-DD'), 'sueldo', 100.00); INSERT INTO pagos VALUES (2, 047, TO_DATE('2022-01-01','YYYY-MM-DD'), 'bono', 10.00); INSERT INTO pagos VALUES (3, 056, TO_DATE('2022-01-15','YYYY-MM-DD'), 'sueldo', 150.00); INSERT INTO pagos VALUES (4, 056, TO_DATE('2022-01-15','YYYY-MM-DD'), 'bono', 20.00); INSERT INTO pagos VALUES (5, 112, TO_DATE('2022-01-30','YYYY-MM-DD'), 'sueldo', 200.00); INSERT INTO pagos VALUES (6, 112, TO_DATE('2022-01-30','YYYY-MM-DD'), 'bono', 25.00); |
| id | id_empleado | fecha | tipo_pago | monto |
|---|---|---|---|---|
| 1 | 47 | 01/01/2022 | sueldo | 100 |
| 2 | 47 | 01/01/2022 | bono | 10 |
| 3 | 56 | 15/01/2022 | sueldo | 150 |
| 4 | 56 | 15/01/2022 | bono | 20 |
| 5 | 112 | 30/01/2022 | sueldo | 200 |
| 6 | 112 | 30/01/2022 | bono | 25 |
Para el reporte de total devengado por mes, solo sería sumar todo el monto y agrupando por mes:
|
1 2 |
SELECT to_char(fecha, 'MM-YYYY') mes, SUM(monto) AS total FROM pagos GROUP BY to_char(fecha, 'MM-YYYY'); |
| mes | total |
|---|---|
| 01-2022 | 505 |
Ahora para sumarlo por separado el sueldo del bono, solo se necesita agrupar adicionalmente por el tipo de pago, que sería la columna tipo_pago:
|
1 2 3 4 5 |
SELECT to_char(fecha, 'MM-YYYY') mes, tipo_pago, SUM(monto) AS total FROM pagos GROUP BY to_char(fecha, 'MM-YYYY'), tipo_pago; |
| mes | tipo_pago | total |
|---|---|---|
| 01-2022 | bono | 55 |
| 01-2022 | sueldo | 450 |
Ok, el resultado funciona y podemos filtrar con un where si necesitamos uno u otro pero esto puede ser un doble trabajo o doble consulta y ¿Qué hay del caso que para el reporte se necesiten dos columnas y no dos filas?
Se complica porque se puede realizar de varias maneras: se puede hacer desde el lado de frontend como también manejando arrays en backend para agrupar por empleado; Hacer dos subquery que totalicen por separado con cada columna, pero esto implica duplicar el query y también los filtros en el where; Hacer concatenación con la función COALESCE y luego separarla en columnas desde código, etc.
Para este caso podemos usar un CASE en la función SUM y así poder separar en dos columnas sin tener que hacer grandes cambios a la consulta y se mantiene aquellos filtros usados en where.
|
1 2 3 4 5 |
SELECT to_char(fecha, 'MM-YYYY') mes, SUM(CASE WHEN tipo_pago = 'sueldo' THEN monto ELSE 0 END) AS total_sueldos, SUM(CASE WHEN tipo_pago = 'bono' THEN monto ELSE 0 END) AS total_bonos FROM pagos GROUP BY to_char(fecha, 'MM-YYYY'); |
| mes | total_sueldos | total_bonos |
|---|---|---|
| 01-2022 | 450 | 55 |
Es importante recordar que la función SUM devolverá NULL si la columna permite NULL y tampoco sino existe algún registro, por ejemplo en el caso de que haya empleados sin bonos y debemos cambiarlo a cero 0 manualmente, ya sea con un NVL o con el ELSE para que sume cero.
