number orders for each year and Month

Question

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.orderdate output

Pivot table: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Nick Fotopoulos, Feb 09 '22 at 17:25
SQL isn't designed to create a dynamic number of columns. You ***can*** write Dynamic SQL (code that reads data, and writes new SQL code specifically for that data, such as creating a new column for each year/month it finds), but that's generally misuse of SQL. The normalised structure you are currently working with is the correct one for SQL; don't try to force a square peg in to a round hole, it'll just make things worse in the long run. If you need to restructure it for display purposes, do that in your application or reporting layer. — MatBailie, Feb 09 '22 at 17:26
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Nick Fotopoulos, Feb 09 '22 at 17:27
What flavour of SQL are you using? Give us the structure of your tables and a sample of the output format you want. — , Feb 09 '22 at 17:28
Which dbms are you using? (The above query is product specific.) — jarlh, Feb 09 '22 at 17:44
the table is from database adventure works 2019 from the table sales order header i add the screenshot in my question — stas2806, Feb 09 '22 at 17:44
create a pivot query using customer_id as the grouping and count the order_ids — Golden Lion, Feb 09 '22 at 18:40

score 1 · Answer 1 · edited Feb 10 '22 at 04:05

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

number orders for each year and Month

1 Answers1