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.
2