Get Sum in month with PostgreSQL

Question

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

for example in the first month the realization has a total of 2000, then in the second month it gets 900 so in the second month it should be, the first month + the income of the second month, so it should be 2900... and so on — Jon
– Jon, Commented Jun 17, 2021 at 3:06
take a look on this article, it has the answer for you problem postgresqltutorial.com/postgresql-lag-function — Francisco de Castro
– Francisco de Castro, Commented Jun 17, 2021 at 3:18

Chass Long · Accepted Answer · 2021-06-17 03:18:38Z

1

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

edited Jun 17, 2021 at 3:18

answered Jun 17, 2021 at 3:05

Chass Long

5495 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Over a year ago

please help on my query

Collectives™ on Stack Overflow

Get Sum in month with PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related