SQL Question 14

Problem

  • USE Northwind
  • Write a script that will loop through all categories of products and display the productname of the top selling product. (by dollar amount)
  • Make sure your numbers display with $ and commas
  • Don’t hard code your loop. In other words your program should work whether there are 8 categories or 10.

Code

USE Northwind
Declare @CategoryId int
Declare @RowNum int
Declare @categoryName varchar(10)
Declare @totalSold numeric(10,2)
Declare @productName varchar(50)

SELECT TOP 1 @CategoryId=categoryID FROM Northwind.dbo.Categories
SET @RowNum = 0

WHILE @RowNum < (SELECT COUNT(CategoryID) FROM Categories)

BEGIN
SET @RowNum = @RowNum + 1
SELECT @categoryName=(SELECT CategoryName FROM Categories
where categoryID= @CategoryId)
SELECT TOP 1 @productName = productname, @totalSold = SUm(od.quantity * od.unitprice)
FROM [Order Details] od JOIN Products p ON od.ProductID = P.ProductID
JOIN Categories c ON c.CategoryID = p.CategoryID
WHERE c.CategoryID = @CategoryId
GROUP BY ProductName
ORDER BY 2 DESC
PRINT CAST(@RowNum as char(2)) + ' ' + @categoryName + ' ' + @productname + ' $' + CONVERT(varchar,CONVERT(money,@totalsold),1)
SELECT TOP 1 @CategoryId=categoryID from Northwind.dbo.Categories
where categoryID > @CategoryId
END

Result

Question 14 Result

Question 14 Result

--Test script to see if I'm getting the right answer
USE NORTHWIND
SELECT c.CategoryName, P.ProductName, SUM(OD.Quantity*OD.UnitPrice)
, RANK()OVER(PARTITION BY C.CATEGORYName
ORDER BY SUM(OD.Quantity*OD.UnitPrice) DESC) AS 'RANK'
FROM [Order Details] OD JOIN Products P ON P.ProductID=OD.ProductID
JOIN Categories c ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryName, P.ProductName
ORDER BY 1

Question 14 Test

Question 14 Test

Notes

This script DECLARE many variables used. The @CategoryID is given the TOP value of the CategoryID from the Categories table. A WHILE Loop is used where @RowNum increments each time by 1.Inside the loop, the @categoryName variable is given the values of CategoryName from the Categories table. A TOP 1 is used with the @productName variable and @totalSold variable to find the top selling product name and values. The PRINT statement includes the row number @RowNum, categoryname @categoryName, productName @productName, and totalsold @totalSold. A tricky part of this problem is concatenating the '$' onto the @totalSold value. This requires two CONVERT statements.

No Comments Yet.

Leave a comment