CODEWITHSUNDEEP
sqlsql-servert-sqlselectwhile-loop
Tim
Tim

Reputation: 71

T-SQL SELECT with multiple variables

I am trying to get this output using a SQL statement and the NORTHWIND database:

Employee Name:Nancy Davolio
Number of Sales:345
Total Sales:192107.60

Employee Name:Andrew Fuller
Number of Sales:241
Total Sales:166537.75

Employee Name:Janet Leverling
Number of Sales:321
Total Sales:202812.84

Employee Name:Margaret Peacock
Number of Sales:420
Total Sales:232890.85

Employee Name:Steven Buchanan
Number of Sales:117
Total Sales:68792.28

...and 4 more entries

When I use this statement:

USE Northwind

DECLARE @EmployeeName VARCHAR(40),
        @NumberOfSales INT,
        @TotalSales DECIMAL(10,2),
        @Counter TINYINT = 1,
        @NumEmployees INT = IDENT_CURRENT('dbo.Employees');

WHILE @Counter < @NumEmployees
BEGIN
    --SELECT @EmployeeName = E.FirstName+' '+E.LastName
    --SELECT @NumberOfSales = count(od.OrderID)
    SELECT @TotalSales = SUM(unitprice * quantity * (1 - Discount))
    FROM Employees E
    JOIN Orders AS O ON O.EmployeeID = E.EmployeeID
    JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID
    WHERE E.EmployeeID = @Counter

    PRINT 'Employee Name:       '--+ @EmployeeName;
    PRINT 'Number of Sales:     '--+ LTRIM(STR(@NumberOfSales));
    PRINT 'Total Sales:         '+CONVERT(varchar(10),@TotalSales);
    PRINT '';

    SET @Counter += 1;
END

I can get each select to work singly but I cannot figure out the syntax to get a single SELECT statement to do all the work. I should also be able to do this with three SET statements but I've not been able to figure that out either. Pointers to both possibilities would be awesome.

Here's that actual step verbiage: "Within the loop, use a SELECT statement to retrieve the first and last name of each employee, the number of orders handled by each employee and the total sales amount for each employee (you are processing each employee one by one). You will need to join multiple tables together and use aggregate functions to get a count and a total. Assign the concatenated full name, number of sales and total sales amount to the appropriate variables."

Output should be in Messages tab, no table or format other than the expected output listed above.

Upvotes: 2

Views: 358

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

There is no need for loop(RBAR - Row By Agonizing Row approach should be avoided if possible):

SELECT EmployeeID
       ,[Employee Name] = E.FirstName+' '+E.LastName
       ,[TotalSales] = SUM(unitprice * quantity * (1-Discount))
       ,[NumberOfSales] = COUNT(DISTINCT o.OrderID)
FROM Employees E
JOIN Orders AS O ON O.EmployeeID = E.EmployeeID
JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID
GROUP BY E.EmployeeID, E.FirstName+' '+E.LastName
ORDER BY E.EmployeeID;

EDIT:

Loop version - assigning multiple variables at once.

USE Northwind

DECLARE @EmployeeName VARCHAR(40),
        @NumberOfSales INT,
        @TotalSales DECIMAL(10,2),
        @Counter TINYINT = 1,
        @NumEmployees INT = IDENT_CURRENT('dbo.Employees');

WHILE @Counter < @NumEmployees
BEGIN
    SELECT @EmployeeName  = E.FirstName+' '+E.LastName
          ,@NumberOfSales = COUNT(DISTINCT o.OrderID)
          ,@TotalSales    = SUM(unitprice * quantity * (1 - Discount))
    FROM Employees E
    JOIN Orders AS O ON O.EmployeeID = E.EmployeeID
    JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID
    WHERE E.EmployeeID = @Counter
    GROUP BY E.FirstName+' '+E.LastName;

    PRINT 'Employee Name:       '+ @EmployeeName;
    PRINT 'Number of Sales:     '+ LTRIM(STR(@NumberOfSales));
    PRINT 'Total Sales:         '+ CONVERT(varchar(10),@TotalSales);
    PRINT '';

    SET @Counter += 1;
END

Please note that using WHILE loop maybe very inefficient when you have gaps(i.e. you are starting from 1 up to IDENT_CURRENT, it may be a situation where you have ids like 1,5, 200671 and you end up with unecessary looping).

EDIT 2:

It seems the GROUP BY is required when multiple assigns take place in the select

I've added GROUP BY because FirstName and LastName was not wrapped with aggregated function. You could skip that clause but then you need to add MIN/MAX function:

SELECT @EmployeeName  = MIN(E.FirstName)+' '+MIN(E.LastName)
      ,@NumberOfSales = COUNT(DISTINCT o.OrderID)
      ,@TotalSales    = SUM(unitprice * quantity * (1 - Discount))
FROM Employees E
JOIN Orders AS O ON O.EmployeeID = E.EmployeeID
JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID
WHERE E.EmployeeID = @Counter;
-- and we are sure that all values for First/Last nane are the same because of  
-- WHERE E.EmployeeID = @Counter

Related: Group by clause

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause

Upvotes: 5

user3623480
user3623480

Reputation:

This should do it. I used CROSS APPLY to unpivot the set and then format it accordingly. You can read more about it in the article called: "CROSS APPLY an Alternative Method to Unpivot". Since SQL works with sets, input and output from SQL should always be a set in my humble opinion.

I am afraid that the way you formatted might not be a SQL's job but still do-able with a "single" select statement as a set operation:

;WITH CTE AS 
(
SELECT 
        EMPLOYEENAME =    E.FirstName +' '+ E.LastName,  
        NUMBEROFORDERS =  COUNT(OD.OrderID),
        TOTALSALES =      SUM(unitprice * quantity * (1-Discount))

   FROM Employees E
   INNER JOIN Orders AS O ON O.EmployeeID = E.EmployeeID
   INNER JOIN [Order Details] AS OD ON OD.OrderID = O.OrderID
   GROUP BY E.FirstName + ' ' + E.LastName
)


SELECT COLNAME, ColValue
FROM CTE

CROSS APPLY ( VALUES ('Employe Name:', EMPLOYEENAME),
                     ('Number of Sales:', LTRIM(STR(NUMBEROFORDERS, 25, 5)) ),
                     ('Total Sales:', LTRIM(STR(TOTALSALES, 25, 5)) ),
                     ('','')

            ) A  (COLNAME, ColValue)

Sample output is following:

COLNAME             ColValue
-------------   | ------------- 
Employe Name:   | Nancy Davolio
Number of Sales:|  345.00000
Total Sales:    |  192107.60432

Upvotes: 1

Related Questions