Cumulative count for a specified value in SQL server

Question

I have a table in Sql Sever that looks like this,

Br     Type    Sales
102     A       20
102     B       10
102     D       8
102     F       12
103     A       18
103     C       7
103     D       15
103     E       3

I want to create a new table in SQL Server that adds a column to the existing table above that provides a cumulative count for each unique Br column and sorts the data Ascending according to the sales column.

The new table should look like this,

BrCount    Br     Type    Sales
1          102     D       8
2          102     B       10
3          102     F       12
4          102     A       20
1          103     E       3
2          103     C       7
3          103     D       15
4          103     A       18

I am not sure where to start. I thought about using some kind of loop that analyse at each iteration what Br number a row contain and update the BrCount column with the previous value + 1 or just a 1 if the Br number from the previous row does not match, but i struggle to find examples on such kind of loops.

Answer 1

You can use the ranking function ROW_NUMBER() to do so:

SELECT 
  ROW_NUMBER() OVER(PARTITION BY br
                    ORDER BY Sales) AS BRCount,
  Br, 
  Type,
  Sales
FROM SalesTable;

• SQL Fiddle Demo

This will give you:

| BRCOUNT |  BR | TYPE | SALES |
|---------|-----|------|-------|
|       1 | 102 |    D |     8 |
|       2 | 102 |    B |    10 |
|       3 | 102 |    F |    12 |
|       4 | 102 |    A |    20 |
|       1 | 103 |    E |     3 |
|       2 | 103 |    C |     7 |
|       3 | 103 |    D |    15 |
|       4 | 103 |    A |    18 |

Answer 2

According to your example, what you are looking for is a query on the existing table using one of the ranking functions:

SELECT 
  Rank() OVER(PARTITION BY br BY Sales) AS BRCount,
  Br, 
  Type,
  Sales
FROM MyTable

You might want to use RANK, DENSE_RANK or ROW_NUMBER. You could read more about the differences in this excellent SO thread.