Results in ascending order with cursor

Question

I have the following My Guitar Shop exercise in my SQL II course and am stumped with the part about getting the results in ascending order through the cursor. Any advice is appreciated.

Here are my instructions:

"Write a script that utilizes a cursor to print the lastname of each customer in the customers table along with the number of orders that customer has placed.

Print the results to the screen in ascending order by number of order placed.

Include a total at the end that shows the total number of orders placed."

Here's my code:

USE MyGuitarShop;

/* Write a script that  utilizes a cursor to print the lastname of each customer in the customers table 
along with the number of orders that customer has placed.  
Print the results to the screen in ascending order by number of order placed. 
Include a total at the end that shows the total number of orders placed.*/

    DECLARE @CustID int;
    DECLARE @CustLastName varchar(60);
    DECLARE @TotalOrders int;

    SET @TotalOrders = 0;
    
    DECLARE Orders_Cursor CURSOR
    FOR
        SELECT CustomerID, LastName
        FROM Customers
    
    OPEN Orders_Cursor;
    
    FETCH NEXT FROM Orders_Cursor INTO @CustID, @CustLastName

    DECLARE @OrderCount int;
        
    WHILE @@FETCH_STATUS <> -1
    BEGIN
        PRINT @CustLastName + ': ' + CONVERT(varchar,@OrderCount);

        SELECT @OrderCount = COUNT(OrderID) 
        FROM Orders
        WHERE CustomerID = @CustID
        ORDER BY COUNT(OrderID) ASC;

        SET @TotalOrders = @TotalOrders + @OrderCount
            
        FETCH NEXT FROM Orders_Cursor INTO @CustID, @CustLastName;
    END;
        
    PRINT 'Total number of orders placed: ' + CONVERT(varchar, @TotalOrders);
        
    CLOSE Orders_Cursor;
    DEALLOCATE Orders_Cursor;

Everything works accept the ascending order and I have spent hours and hours and cannot figure this out. Any tips to lead me in the right direction is appreciated.

Answer 1

If I understand your question correctly, you just need list all the customers with the order count.

You can create one cursor directly based on below SQL statement:

SELECT c.CustomerID, c.LastName, COUNT(o.orderid) as orderCount FROM 
customers c LEFT JOIN  orders o
ON c.LastName = o.LastName GROUP BY c.LastName ORDER BY orderCount;

In your implementation, in the sub-sql, you query the order count for a specified customer and order by , the order by is useless for only one customer.