AdventureWorks in SQL Server 2019 : ordershare percent for each item per month

Question

I'm new to SQL Server. I'm trying to write a code to find the sale percent/per item/per month. Something like this:

Year	Month	ProductID	Order_Quantity_Per_Month	Total_Sold_Per_Month	%_Of_Total_Sale
2011	5	707	422	17024	2

1. First and most importantly, I want to write this code with "CTE" and "Group by". I've tried many times but I failed. How can I write this code with cte and group by?

2. I wrote this code with "Over" and "Partition". Could someone check the codes I've written to see if it's actually correct:

   USE AdventureWorks2019
   GO
   
   SELECT
       YEAR (soh.OrderDate) As Year,
       MONTH (soh.OrderDate) As Month,
       pro.productid AS [Product ID],
       pro.Name AS [Product Name],
       SUM(sod.OrderQty) OVER (PARTITION BY Month(soh.OrderDate) ORDER BY by soh.OrderDate) AS [Order Quantity Per Month],
       SUM(sod.OrderQty) OVER (PARTITION BY Month(soh.OrderDate)) AS [Total Sold Per Month],
       SUM(sod.OrderQty) OVER (PARTITION BY Month(soh.OrderDate) ORDER BY by soh.OrderDate) * 100 / SUM(sod.OrderQty) OVER (PARTITION BY Month(soh.OrderDate)) AS [% of TotalSale]
   FROM      
       Production.Product pro
   INNER JOIN  
       Sales.SalesOrderdetail sod ON pro.ProductID = sod.ProductID
   INNER JOIN  
       Sales.SalesOrderheader soh ON soh.SalesOrderID = sod.SalesOrderID
   GROUP BY
       YEAR(soh.OrderDate), MONTH(soh.OrderDate),
       soh.OrderDate, pro.productid, pro.Name, sod.OrderQty
   ORDER BY 
       Year, Month
   

If the above code is correct, How can I write the code with cte and group by?

Answer 1

I think the better question is why you want (or need) to use a CTE. A simple CTE (i.e., not recursive) is just syntactic sugar for a derived table. There is nothing particular special or complicated about writing and using one in a query. If you "tried many times", you should have included those attempts in your question.

But to satisfy the need to use a CTE, you can simply "cram" the query you have into the CTE and select rows from it. Example:

with cteOrders as (
    select ... -- your original query here without ORDER BY clause
)
select * from cteOrders 
order by [Year], [Month]
;

That is a extremely simplistic way of using a CTE. There is no real or obvious advantage to doing so but it does satisfy your goal. Because of that, I smell a XY problem.