[SOLVED] Trying to show Product Name instead of ID for sales query using Adventure Works database

Trying to show Product Name instead of ID for sales query using Adventure Works database

I am struggling with getting the product name to show instead of the ID. Any thoughts?

SELECT TOP 10 ProductID, SUM(LineTotal) AS SumOfSales
FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE SOH.OrderDate > '12/31/2012' AND SOH.OrderDate < '1/1/2014'
GROUP BY ProductID
--HAVING SUM(LineTotal) >= 2000000
ORDER BY SUM(LineTotal) DESC

Solution

You need to add the product table because the product name is available only there. Join the product table on primary and foreign keys to get the required column from the table.

SELECT TOP 10 sod.ProductID,prd.<add the required column>, SUM(LineTotal) AS SumOfSales
FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
join Production.Product prd on prd.ProductID  = sod.ProductID
WHERE SOH.OrderDate > '12/31/2012' AND SOH.OrderDate < '1/1/2014'
GROUP BY sod.ProductID,prd.<add the required column>
--HAVING SUM(LineTotal) >= 2000000
ORDER BY SUM(LineTotal) DESC