How to calculate the cumulative sum in Oracle

Asked

Viewed 2,244 times

1

Hello,

I have a table with a lot of data, and I would like to count the amount of records in each date of this month. So far OK, a COUNT(*) and a GROUP BY already resolves.

SELECT
    Trunc(td.data_emissao) AS data_emissao
    ,Count(*) AS quantidade
FROM
    tabela_de_dados td
WHERE
    Trunc(td.data_emissao,'mm') = Trunc(Sysdate,'mm')
GROUP BY
    Trunc(td.data_emissao)
ORDER BY
    1 ASC
;

But I also need to know cumulative amount, example; the quantity of day 2 is the sum of the records from day 1 to day 2, and so on, until on the last day I have the total records of the month.

At the end, I would like to return to the table below, as an example

Data_emissao | Quantidade | Quantidade Acumulada
--------------------------------------------------
01/04        | 10         | 10
02/04        | 25         | 35
03/04        | 15         | 50
04/04        | 30         | 80
...          | ...        | ...

And so on from the whole month. I hope to have clarified the doubt.

sql oracle

  • Wouldn’t be the same count(*) but without grouping by date, only leaving in the condition where the month and the year you want the total? I don’t know if I understand your doubt well.

  • It would work, if I searched day by day the total until that moment. But what I need is the two information in the same table. Example Date 01/04 Records 10 Accumulated Records 10; Date 02/04 Records 25 Accumulated Records 35 - and so on

  • 1

    Search for "Oracle OVER Analytic Function()"

  • Thanks @Motta! Got it!

  • Analytic Function allow very useful reports !!

2 answers

3


I managed using "Oracle Analytic Function" "Sum() Over()"

    select
       trunc(td.data_emissao) as data_emissao
       ,count(*) as emitidos
       ,sum(count(*)) over(order by trunc(td.data_emissao) rows between unbounded preceding and current row) as emitidos_acumulado
    from
       tabela_de_dados td
    where
       trunc(td.data_emissao,'mm') = trunc(sysdade,'mm')
    group by
       trunc(td.data_emissao)

2

Try:

SELECT
    Trunc(td.data_emissao) AS data_emissao,
    Count(*) AS quantidade,
    (SELECT count(*) FROM tabela_dados tda 
     WHERE tda.trunc_emissao <= td.data_emissao AND Trunc(tda.data_emissao,'mm') = Trunc(Sysdate,'mm')) AS qtd_acumulada
FROM
    tabela_de_dados td
WHERE
    Trunc(td.data_emissao,'mm') = Trunc(Sysdate,'mm')
GROUP BY
    Trunc(td.data_emissao)
ORDER BY
    1 ASC
;

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.