Skip to content
Advertisement

Displaying a sum incrementally per row using window function in postgresql

I need to enter the total length of verblijfsduur (stay) per reisnr (trip) incrementally according to the order in which the celestial objects are visited.

This what I need: (ignore the tot_duur column, that is the sum of all verblijfsduur)

enter image description here

This what I get, see it dosn’t show incrementally according to order, it shows the total perreisnr (trip number):

enter image description here

I don’t know how I could indicate this in the PARTITION BY part of my query:

SELECT re.reisnr, be.volgnr, be.objectnaam, be.verblijfsduur
,sum(be.verblijfsduur) OVER (PARTITION BY re.reisnr ORDER BY re.reisnr ) as 
inc_duur
FROM reizen re INNER JOIN bezoeken be ON re.reisnr = be.reisnr
ORDER BY re.reisnr, be.volgnr, be.objectnaam, be.verblijfsduur

Advertisement

Answer

I found the problem, thanks/sorry if you had an answer, I had to order by be.volgnr

SELECT re.reisnr, be.volgnr, be.objectnaam, be.verblijfsduur
,SUM(be.verblijfsduur) OVER (PARTITION BY re.reisnr ORDER BY be.volgnr) as inc_duur

FROM reizen re INNER JOIN bezoeken be ON re.reisnr = be.reisnr
ORDER BY re.reisnr, be.volgnr, be.objectnaam, be.verblijfsduur
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement