CODEWITHSUNDEEP
sqlsql-servercount
Justin
Justin

Reputation: 33

SQL Get Weekly Count of product items

I am trying to get the total number entries into a table per week per product. Every time someone orders a product, it is entered into a database with the following information:

Table: Orders

order_id
order
order_date

There are 8 options for "order", which are pre-set and are never changing. I need to see the count per week of the items that are in that table..

I want to see something like this:

 Order     week 1    week 2    week 3    week 4 etc...

 order1    30         10       11         23
 order2    40          4        0         44
 order3    88         23       12         22

Can someone tell me how that is achieved??

Thanks.. I'm so stuck on this.

Upvotes: 1

Views: 1077

Answers (1)

Michael Ludwig
Michael Ludwig

Reputation: 197

as NickyvV indicated you want a pivot. However SQL Server cannot accept dynamic columns in a pivot, so you have to spell them out. Also you probably don't want the order_id in the first column, since you would only get 1s and 0s in the counts, but something like the product_id instead. So you could do something like this:

SELECT 
product_id,
[week 1],[week 2],[week 3],[week 4],[week 5]
FROM
        (SELECT 
        product_id,
        'week ' + cast(DatePart(wk,[order_date]) as varchar(2)) as WeekName
        FROM [Orders]) as o
pivot (count(WeekName) 
for WeekName in ([week 1],[week 2],[week 3],[week 4],[week 5])) as p

Unfortunately, you'll have to spell out all possible week names. There would be ways using dynamic SQL, but i wouldn't recommend them as they are rather complex and nasty. Also, remember that if this is going into a report, you normally would do this pivoting in the reporting application (SSRS, etc.) instead of in the db.

Upvotes: 1

Related Questions