SQL Question 13

Problem

  • USE Northwind
  • Write a script that will PRINT the count of orders for EACH customer
  • Declare all variables with appropriate data types
  • Loop through the data
  • Use the PRINT statement to display the information in the Message tab

Code

Use Northwind

Declare @CustId nchar(5)
Declare @RowNum int
Declare @OrderCount int

SELECT TOP 1 @CustId=CustomerID FROM Northwind.dbo.Customers
SET @RowNum = 0

WHILE @RowNum < (SELECT COUNT(customerid) FROM Customers)

BEGIN
SET @RowNum = @RowNum + 1
SELECT @ordercount = Count(Orderid) FROM Northwind.dbo.Orders
where CustomerId = @CustID
PRINT CAST(@RowNum as char(2)) + ' ' + @CustId + ' ' + CAST(@ordercount as varchar)
SELECT TOP 1 @CustId = CustomerID FROM Northwind.dbo.Customers
where CustomerId > @CustID
END

Result

Question 13 Result

Question 13 Result

Notes

This script starts with DECLARE statements defining the variables used with associated data types. The @CustId variable is then assigned an initial value equal to CustomerID from the Customers table. The @RowNum variable is then SET to 0 as it is used in a WHILE loop. The WHILE loop COUNT the customerid from Customers and stops when @RowNum is no longer less than the COUNT of customerid. The script has BEGIN and END statements. The @RowNum is SET to increment by 1 each time. The WHILE LOOP COUNT each OrderID from the Orders table and stores that value in the @ordercount variable. This COUNT of the OrderID is done for each CustomerID stored in the @CustID variable. Finally, the TOP 1 is used providing one result per customerID (not sure on this).

Comments are closed.