CODEWITHSUNDEEP
sqlpostgresqlsumwindow-functions
rennatnart
rennatnart

Reputation: 23

How to get distinct sums when summing over repeated values?

The issue I am having is with Postgres, in that using SUM with non-unique values or "sales" results in non-unique cumulative sums or "running sales".

Here is the query:

SELECT *, 
       SUM(Sales) OVER (ORDER BY sales DESC) as running_sales 
FROM t;

Here is the output

Essentially I want this table to read:

4--Motherboards------- 22----399
5--RAID Controllers----22----421

Does anyone have any ideas on how I could do this?

Aside: If anyone is interested, this is what I was following

Upvotes: 2

Views: 1876

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

Make the order in the OVER clause unique by adding id as second ORDER BY item:

SELECT *, 
       SUM(Sales) OVER (ORDER BY sales DESC, id) as running_sales 
FROM   t
ORDER  BY sales DESC, id;

The way you had it, equal sales are treated as peers in the same FRAME and summed at once.
The tutorial you were following is no good for this particular query.

Also, you probably want to add an ORDER BY to the query itself to get stable results. This is very cheap, since it agrees with the sort order of the window function.

The way it is implemented right now, you normally get the results from this query in the order of the window function for simple queries, because that's the cheapest way. But Postgres makes no guarantee about the ordering of rows without ORDER BY, the arbitrary order can change without warning.

Upvotes: 3

Related Questions