CODEWITHSUNDEEP
sqlsql-server
Darkskies16
Darkskies16

Reputation: 71

MSSQL query to get weekno from date, groupby weekno and sum sales

Is this possible in sql alone?

I have a table which contains the following rows

 StoreID |  Date      | SalesItem |
 1       | 2016-08-16 |  Book    |
 2       | 2016-08-16 | Pen      |
 1       | 2016-08-15 | Pen      |
 1       | 2016-08-15 | Book     |

The results I want would be 

Store | Week | Sales
1     | 11   | 30
2     | 11   | 15

I'm using sql server 2008, the data set is much larger than the above example but that's basically what i would want to achieve in sql without processing in PHP afterwards.

What I have so far is

 select [DATE], [store], count(store) as total, DATEPART(ww,DATE) 
 AS weeknum from [contracts] where [DATE] >= DATEADD(month, -12, GetDate())
 group by [DATE], [store] order by [DATE] asc

Upvotes: 0

Views: 44

Answers (1)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try the below code

    SELECT Store,DATEPART(WW,Date),SUM(Sales) FROM TABLE1
    GROUP BY Store, DATEPART(WW,Date)

As per edit of Question

    SELECT Store,DATEPART(WW,Date),count(Sales) FROM TABLE1
    GROUP BY Store, DATEPART(WW,Date)

Upvotes: 2

Related Questions