Sum ordered rows OVER with distinct duplication

Question

This is the data I have:

Customer	Sales
A	3
B	10
C	4
D	2
E	4

This is where I want to get:

# Top Customers	Total Sales
1	10
2	14
3	18
4	21
5	23

I am trying to do this with ROW_NUMBER and OVER (a window function?):

SELECT 
  ROW_NUMBER() OVER (ORDER BY Sales DESC) AS '# TOP CUSTOMERS',
  SUM(Sales) OVER (ORDER BY Sales DESC) AS 'SUM SALES'
FROM Sales;

but I get this result:

# Top Customers	Total Sales
1	10
2	18
3	18
4	21
5	23

Do I need to use DENSE_RANK or PARTITION BY? For what it's worth, I'm not even trying to rank or partition, I'm just trying order the rows, so trying to see if there's a simple solution.

OVER (ORDER BY Sales DESC, customer) to ensure the ordering never has duplicates and is deterministic... db-fiddle.com/f/7GwR7PQwYufFTEDGyu3JUH/0 — MatBailie
– MatBailie, Commented Sep 18, 2024 at 23:21
@MatBailie This works great, should have been obvious. Thanks — gust
– gust, Commented Sep 19, 2024 at 0:06

sowmya.saguturu · Accepted Answer · 2024-09-19 04:25:45Z

0

You should include Customer as well to make the row unique. See the image

SELECT 
  ROW_NUMBER() OVER (ORDER BY Sales DESC) AS '# TOP CUSTOMERS',
  SUM(Sales) OVER (ORDER BY  Sales DESC, Customer ASC ) AS 'SUM SALES'
FROM Sales;

Click me for results

answered Sep 19, 2024 at 4:25

sowmya.saguturu

112 bronze badges

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

Collectives™ on Stack Overflow

Sum ordered rows OVER with distinct duplication

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related