CODEWITHSUNDEEP
sqlsql-servercountcounting
Van Diesel
Van Diesel

Reputation: 69

SQL Server : counting all products and sales data

I have two tables Product and Sales with the following structure:

Product table:

ProductID | Name
     1    | Product 1
     2    | Product 2
     3    | Product 3

Sales table:

    SalesID   | Time     | ProductID |  Sales
       01     |  201601  |     1     |    10
       02     |  201602  |     1     |    11
       03     |  201603  |     1     |    13
       04     |  201604  |     2     |    1
       05     |  201601  |     3     |    12
       06     |  201602  |     2     |    5
       06     |  201608  |     3     |    3
       06     |  201609  |     3     |    4

I would like to get this view:

    ProductName   |   Time    |   counter |   Sales
       Product 1  |  201601   |     0     |    10
       Product 1  |  201602   |     1     |    11
       Product 1  |  201603   |     2     |    13
       Product 2  |  201604   |     0     |    1
       Product 3  |  201601   |     0     |    12
       Product 2  |  201602   |     1     |    5
       Product 3  |  201608   |     1     |    3
       Product 3  |  201609   |     2     |    4

So basically my counter is just a column that shows me the first sales depending on product and time. My counter must starting with 0 and it count how many sales do I have for this product.

For example for Product 1 the first sales is in 201601 the second one is in 201602 etc.

For each sales depending on product I need to do: Counter = counter + 1 and if I have a new product my counter must start with 0 again.

I can do this with stored procedures but if it’s possible I would like to create a query for this part.

Thanks for your help.

Upvotes: 1

Views: 75

Answers (2)

Phyiber
Phyiber

Reputation: 41

As Kumar pointed out you can use row number with partitions when it is simply sequential:

SELECT
    p.Name,
    s.Time,
    (ROW_NUMBER() OVER (PARTITION BY s.ProductId ORDER BY s.ProductId, s.Time) - 1) AS Counter,
    s.Sales
FROM [Sales] s
LEFT JOIN [Product] p ON p.ProductId = s.ProductId

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are looking for row_number():

select p.productname, s.time,
       row_number() over (partition by s.productid order by time) as counter,
       s.sales
from sales s join
     product p
     on s.productid = p.productid
order by time;

Upvotes: 2

Related Questions