CODEWITHSUNDEEP
sqlpostgresql
Jon
Jon

Reputation: 109

Get Sum in month with PostgreSQL

I have the following SQL where I display the total by month:

select to_char(tanggal, 'mm') as month, 
      sum(nilai) as realisasi
    from 
      tbl_pasar_dini 
    where (date_part('year', tanggal)= extract(year from timestamp 'NOW()')) 
    group by 1

and this is the result:

month realisasi
01 2000
02 900
03 3000
04 200
05 5000
06 100

How do I make the total accumulate, so that each month the value increases from the previous month

Upvotes: 0

Views: 611

Answers (1)

Chass Long
Chass Long

Reputation: 539

From what I am interpreting from your question you want the total sum of every realisasi value of all the months int the current given year of 2021. So you could either reuse your given query and do this

select sum(realisasi) from (/* the select query you given */)

or just a little modification to just get the entire sum

select sum(nilai) as realisasi
    from 
      tbl_pasar_dini 
    where (date_part('year', tanggal)= extract(year from timestamp 'NOW()'))

if you could also provide your table structure it would help.

Edit: so from your new comment what you want is a running sum from each month. If so then you have to use the OVER clause.

select to_char(tanggal, 'mm') as month,
      sum(sum(nilai)  OVER (ORDER BY month)) as realisasi
    from 
      tbl_pasar_dini 
    where (date_part('year', tanggal)= extract(year from timestamp 'NOW()')) 
    group by 1

Upvotes: 1

Related Questions