CODEWITHSUNDEEP
sqldatepart
stas2806
stas2806

Reputation: 21

number orders for each year and Month

the outputhow can i output number orders for each year and Month

I have tried the code but it gives me two columns instead of 4, by each year and month

select   month (orderdate), year (orderdate)  ,
   count (orderdate) over (partition by year(orderdate) Order By year(orderdate) desc)  as countoforders
   from sales.SalesOrderHeader
   group by  year (orderdate), month (orderdate),orderdate

sales.SalesOrderHeader.orderdateoutput

Upvotes: 0

Views: 969

Answers (1)

stas2806
stas2806

Reputation: 21

Well I found the answer :

Select year(o.OrderDate)  as yy , MONTH (o.OrderDate) as MM , count(CustomerID) as count
   from sales.SalesOrderHeader o
   group by year(o.OrderDate), MONTH (o.OrderDate)
   order by yy




Select MM,[2011], [2012], [2013], [2014]
     from (Select year(OrderDate)  as yy , MONTH(OrderDate) as MM , CustomerID
        from sales.SalesOrderHeader) o
        PIVOT(count(CustomerID) for yy in ([2011], [2012], [2013], [2014])) rrr 
         order by MM

Upvotes: 1

Related Questions