Return values for all months of the year by drawing per month

Question

Return values for all months of the year by drawing per month

Navigation

#1 by (3 votes)

3

I have 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);

What 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 tabela_exemplo table for the other months of the year, but would like to return all 12 months of the year from that select with a value of 0, just to fill in the chart and do not let that be treated on 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 will do this using HQL. For now I'm checking how the SELECT would be directly by the database (which in this case is PostgreSQL).

sql java postgresql hibernate hql

asked by anonymous 20.10.2015 / 21:13

1 answer

Doubt about the error "Strict standards: Only variables should be passed by reference in" List element zeros after loop for

score 3 · Accepted Answer

The name of this technique is PIVOT .

Try something similar to this 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