CODEWITHSUNDEEP
sqlsql-serverdatabase
RalphBiggerton
RalphBiggerton

Reputation: 199

SQL - Adding Counts next to Customers & Order Dates

I'm trying to place a resetting count of orders next to customers and order dates, but not sure what the right way to approach it would be.

The table would be customer ID, order date, and what order number it was for the customer (order 1 for the customer, order 2 for the customer, etc) with date repeating if they made two orders on the same date.

Right now the data looks like the following using a simple "order / group by" 

| CustomerID  |  OrderNumber   |          Date           |
----------------------------------------------------------
| 0           |   1            | 2010-12-17 00:00:00.000 |
| 0           |   2            | 2011-08-23 00:00:00.000 |
| 0           |   3            | 2011-08-29 00:00:00.000 |
| 0           |   4            | 2011-08-31 00:00:00.000 |
| 1           |   5            | 2007-10-23 00:00:00.000 |
| 1           |   6            | 2007-10-23 00:00:00.000 |
| 2           |   7            | 2004-12-09 00:00:00.000 |
| 2           |   8            | 2011-08-24 00:00:00.000 |
| 2           |   9            | 2011-08-24 00:00:00.000 |
| 3           |   10           | 2004-12-09 00:00:00.000 |
| 3           |   11           | 2011-08-24 00:00:00.000 |
| 3           |   12           | 2011-08-24 00:00:00.000 |
----------------------------------------------------------

I want to make it look like this, I can get the first three columns but not the third to reset after each customer number 

| CustomerID  |  OrderNumber   |       Date              | Order Count |
------------------------------------------------------------------------
| 0           |   1            | 2010-12-17 00:00:00.000 | 1           |
| 0           |   2            | 2011-08-23 00:00:00.000 | 2           |
| 0           |   3            | 2011-08-29 00:00:00.000 | 3           |
| 0           |   4            | 2011-08-31 00:00:00.000 | 4           |
| 1           |   5            | 2007-10-23 00:00:00.000 | 1           |
| 1           |   6            | 2007-10-23 00:00:00.000 | 2           |
| 2           |   7            | 2004-12-09 00:00:00.000 | 1           |
| 2           |   8            | 2011-08-24 00:00:00.000 | 2           |
| 2           |   9            | 2011-08-24 00:00:00.000 | 3           |
| 3           |   10           | 2004-12-09 00:00:00.000 | 1           |
| 3           |   11           | 2011-08-24 00:00:00.000 | 2           |
| 3           |   12           | 2011-08-24 00:00:00.000 | 3           |
| 3           |   13           | 2011-09-24 00:00:00.000 | 4           |
| 3           |   14           | 2013-08-24 00:00:00.000 | 5           |
------------------------------------------------------------------------

here's where I'm at so far

SELECT        CustomerID, OrderNumber, Date
FROM            dbo.Orders
GROUP BY CustomerID, OrderNumber, Date
order by CustomerID

Thanks

Upvotes: 0

Views: 152

Answers (1)

3BK
3BK

Reputation: 1348

How about this?

SELECT 
     CustomerID
    ,OrderNumber
    ,[Date]
    ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderNumber) AS OrderCount
FROM dbo.orders
;

Upvotes: 4

Related Questions