Return values of all months of the year extracting by month

Question

I’m with the following SELECT:

SELECT coalesce(sum(vs.total), 0) FROM tabela_exemplo t
WHERE extract(year from t.data_entrada) = extract(year from current_date)
GROUP BY extract(month from t.data_entrada);

Which returns the following result:

171000.00 -- Referente ao mês de Setembro
21000.00 -- Referente ao mês de Outubro

There are no records in the table tabela_exemplo for the other months of the year, but I would like to return all 12 months of the year from this select with value equal to 0, only to fill in the graph and not let it be treated in the Java server, but only in the database. Example:

-- Outro meses antes de Setembro 
171000.00 -- Referente ao mês de Setembro
21000.00 -- Referente ao mês de Outubro
0.00 -- Referente ao mês de Novembro
0.00 -- Referente ao mês de Dezembro

I’ll do it using HQL. For now I’m checking how the SELECT directly by the database (which in this case is Postgresql).

Answer 1

The name of this technique is PIVOT.

Try something similar with your SQL statement.

SELECT INFO.CODIGO, INFO.CFILIAL, INFO.ANO,
    SUM(INFO.MES01) AS MES01,
    SUM(INFO.MES02) AS MES02,
    SUM(INFO.MES03) AS MES03,
    SUM(INFO.MES04) AS MES04,
    SUM(INFO.MES05) AS MES05,
    SUM(INFO.MES06) AS MES06 
FROM (
    SELECT
        TAB.CODIGO, TAB.CFILIAL, TAB.ANO,
        CASE WHEN TAB.MES = MONTH(DATEADD(MONTH, -5, GETDATE())) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES06,
        CASE WHEN TAB.MES = MONTH(DATEADD(MONTH, -4, GETDATE())) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES05,
        CASE WHEN TAB.MES = MONTH(DATEADD(MONTH, -3, GETDATE())) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES04,
        CASE WHEN TAB.MES = MONTH(DATEADD(MONTH, -2, GETDATE())) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES03,
        CASE WHEN TAB.MES = MONTH(DATEADD(MONTH, -1, GETDATE())) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES02,
        CASE WHEN TAB.MES = MONTH(GETDATE()) THEN SUM(TAB.VALOR_TOTAL) ELSE 0 END AS MES01

    FROM (
        SELECT 
            MONTH(NFSAIDA.DATA) AS MES, YEAR(NFSAIDA.DATA) AS ANO,
            CLIENTES.CODIGO, CLIENTES.CFILIAL, NOTAFISCALSAIDA.VALOR_TOTAL + NOTAFISCALSAIDA.DESPESAS AS VALOR_TOTAL
        FROM NOTAFISCALSAIDA                                      
            INNER JOIN CLIENTES ON (NOTAFISCALSAIDA.CODIGO = CLIENTES.CODIGO AND NOTAFISCALSAIDA.CFILIAL = CLIENTES.CFILIAL)
        WHERE CLIENTES.CODIGO BETWEEN 0 AND 999

    ) AS TAB
    GROUP BY TAB.CODIGO, TAB.CFILIAL, TAB.ANO, TAB.MES
) AS INFO
GROUP BY INFO.CODIGO, INFO.CFILIAL, INFO.ANO
GROUP BY INFO.CODIGO, INFO.CFILIAL, INFO.ANO