Comparison of online sales by product category-SQL

Question

I am beginner to SQL and I need to compare online sales by product category in each country in the first quarter of the last three years. I want a query in adventureworks.How should I do that?Thanks

Answer 1

SELECT Co.Name              AS Country
     , C.Name               AS Category
     , YEAR(OH.OrderDate)   AS [Year]
     , DATEPART(QUARTER,OH.OrderDate) AS [Quater]
     , SUM(OD.LineTotal)    AS Sales
FROM       [Sales].[SalesOrderDetail]        OD 
INNER JOIN [Sales].[SalesOrderHeader]        OH  ON OD.SalesOrderID = OH.SalesOrderID
INNER JOIN [Production].[Product]            P   ON OD.ProductID = P.ProductID
INNER JOIN [Production].[ProductSubcategory] SC  ON P.ProductSubcategoryID = SC.ProductSubcategoryID
INNER JOIN [Production].[ProductCategory]    C   ON SC.ProductCategoryID = C.ProductCategoryID
INNER JOIN [Person].[BusinessEntity]         Pe  ON Pe.BusinessEntityID = OH.CustomerID
INNER JOIN [Person].[BusinessEntityAddress]  A   ON A.BusinessEntityID = Pe.BusinessEntityID
INNER JOIN [Person].[Address]                AD  ON AD.AddressID = A.AddressID
INNER JOIN [Person].[StateProvince]          SP  ON AD.StateProvinceID = SP.StateProvinceID
INNER JOIN [Person].[CountryRegion]          Co  ON SP.CountryRegionCode = Co.CountryRegionCode
WHERE OH.OnlineOrderFlag = 1
 AND DATEPART(QUARTER,OH.OrderDate) = 1
 AND OH.OrderDate >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) -3, 0) 
GROUP BY Co.Name , C.Name , YEAR(OH.OrderDate), DATEPART(QUARTER,OH.OrderDate)