Simplifying query

Question

Is it possible to achieve the same result with a simpler query?

SELECT
    DEPARTAMENTO,
    COALESCE(SUM(MES_01), 0) AS TOTAL_MES_01,
    COALESCE(SUM(MES_02), 0) AS TOTAL_MES_02,
    COALESCE(SUM(MES_03), 0) AS TOTAL_MES_03

FROM
    (
         SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            FALTAS AS MES_01,
            0 AS MES_02,
            0 AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 12)
        UNION ALL
        SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            0 AS MES_01,
            FALTAS AS MES_02,
            0 AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 11)
        UNION ALL
        SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            0 AS MES_01,
            0 AS MES_02,
            FALTAS AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 10)

    ) subquery GROUP BY departamento
    union all
    SELECT
    'Total Geral' AS DEPARTAMENTO,
    COALESCE(SUM(MES_01), 0) AS TOTAL_MES_01,
    COALESCE(SUM(MES_02), 0) AS TOTAL_MES_02,
    COALESCE(SUM(MES_03), 0) AS TOTAL_MES_03
FROM
    (
        SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            FALTAS AS MES_01,
            0 AS MES_02,
            0 AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 12)
        UNION ALL
        SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            0 AS MES_01,
            FALTAS AS MES_02,
            0 AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 11)
        UNION ALL
        SELECT
            DW_DIM_DEPARTAMENTO_PADRAO.DESCRICAO DEPARTAMENTO,
            0 AS MES_01,
            0 AS MES_02,
            FALTAS AS MES_03
        FROM
DW_FATO_INDICADORES_RH  JOIN DW_DIM_DEPARTAMENTO_PADRAO ON DW_FATO_INDICADORES_RH.DEPARTAMENTO = DW_DIM_DEPARTAMENTO_PADRAO.DW_DIM_DEPARTAMENTO_PADRAO_ID        WHERE
            TO_DATE(TO_CHAR(data, 'MM/YYYY'), 'MM/YYYY') = ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE, 'MM/YYYY'), 'MM/YYYY'), - 10)
    ) subquery;

This query sums a field "faltas" by each month and each "departamentos" and brings a table like this

Departamento	Total_mes_01	Total_mes_02	Total_mes_03
Administration	5	2	1
Logistics	2	4	1
Total Geral	7	6	2

The problem is that in real world I am bringing 13 months and is very annoing when I have to make any change, but I could not simplify it with my current knowledge

Answer 1

You can use conditional aggregation to calculate multiple sums covering different months in a single query. This is a technique where you place a CASE expression inside a COUNT(), SUM(), or other aggregate function to limit the data included in the result.

You can also use GROUP BY ROLLUP to get a total across all departments from the same query. In the final select list, a CASE WHEN GROUPING(...) ... expression can be used to inject an appropriate totals label. GROUPING() can also be used in the ORDER BY.

This allows everything to be reduced down to a single select. Something like:

SELECT
    CASE
        WHEN GROUPING(D.DESCRICAO) = 1
        THEN 'Total Geral'
        ELSE D.DESCRICAO
        END AS DEPARTAMENTO,
    COALESCE(SUM(CASE 
        WHEN I.DATA >= ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -12)
        AND I.DATA < ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -11)
        THEN I.FALTAS END), 0) AS TOTAL_MES_01,
    COALESCE(SUM(CASE
        WHEN I.DATA >= ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -11)
        AND I.DATA < ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -10)
        THEN I.FALTAS END), 0) AS TOTAL_MES_02,
    COALESCE(SUM(CASE
        WHEN I.DATA >= ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -10)
        AND I.DATA < ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -9)
        THEN I.FALTAS END), 0) AS TOTAL_MES_03
FROM DW_FATO_INDICADORES_RH I
JOIN DW_DIM_DEPARTAMENTO_PADRAO D
    ON I.DEPARTAMENTO = D.DW_DIM_DEPARTAMENTO_PADRAO_ID
WHERE I.DATA >= ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -12) -- Limit the overall select
AND I.DATA < ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -9)
GROUP BY ROLLUP (D.DESCRICAO)
ORDER BY
    GROUPING(D.DESCRICAO), -- 0 = details first, 1 = totals last
    D.DESCRICAO

I also made a few other changes to the query:

1. The TRUNC() and ADD_MONTHS() have been used to calculate start and end dates for each month instead of converting to and from text
2. An overall WHERE condition was added to limit the selected data to just the three months of interest. By pre-calculating the date limits and comparing I.DATA directly with those date limits, oracle should be able to use available indexes to efficiently access the required range of data.
3. Aliases were defined for the tables references and used to qualify all column references.

Sample results:

DEPARTAMENTO	TOTAL_MES_01	TOTAL_MES_02	TOTAL_MES_03
AAAA	33.33	777.77	3333.33
BBBB	88.88	0	99.99
Total Geral	122.21	777.77	3433.32

See this db<>fiddle for a demo.