Stored Procedure Select from 3 tables

Question

I have three tables in my database Sales, SalesPeople and Appliances.

Sales

SaleDate   EmployeeID AppID Qty
---------- ---------- ----- -----------
2010-01-01 1412       150   1
2010-01-05 3231       110   1
2010-01-03 2920       110   2
2010-01-13 1412       100   1
2010-01-25 1235       150   2
2010-01-22 1235       100   2
2010-01-12 2920       150   3
2010-01-14 3231       100   1
2010-01-15 1235       300   1
2010-01-03 2920       200   2
2010-01-31 2920       310   1
2010-01-05 1412       420   1
2010-01-15 3231       400   2

SalesPeople

EmployeeID EmployeeName                   CommRate    BaseSalary  SupervisorID
---------- ------------------------------ ----------- ----------- ------------
1235       Linda Smith                    15          1200        1412
1412       Anne Green                     12          1800        NULL
2920       Charles Brown                  10          1150        1412
3231       Harry Purple                   18          1700        1412

Appliances

ID   AppType              StoreID Cost          Price
---- -------------------- ------- ------------- -------------
100  Refrigerator         22      150           250
110  Refrigerator         20      175           300
150  Television           27      225           340
200  Microwave Oven       22      120           180
300  Washer               27      200           325
310  Washer               22      280           400
400  Dryer                20      150           220
420  Dryer                22      240           360

How can I obtain this result? (That displays the profitability of each of the salespeople ordered from the most profitable to the least. Gross is simply the sum of the quantity of items sold multiplied by the price. Commission is calculated from the gross minus the cost of those items (i.e. from qty*(price-cost)). Net profit is the total profit minus commission.)

Name          Gross Commission Net Profit
------------- ----- ---------- ---------
Charles Brown 2380  83.5       751.5
Linda Smith   1505  83.25      471.75
Harry Purple  990   65.7       299.3
Anne Green    950   40.2       294.8

My attempt:

CREATE PROC Profitability AS
    SELECT 
        sp.EmployeeName, (sum(s.Qty) * a.Price) as [Gross], 
        [Gross] - a.Cost, as [Commision],
        SOMETHING as [Net Profit]
    FROM 
        Salespeople sp, Appliances a, Sales s
    WHERE 
        s.AppID = a.ID 
        AND sp.EmployeeID = s.EmployeeID
    GROUP BY
        sp.EmployeeName
GO

EXEC Profitability

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-06-05T11:50:51

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

In addition to fixing the JOIN syntax, your query needs a few other enhancements for the aggregation functions:

SELECT sp.EmployeeName, sum(s.Qty * a.Price) as Gross, 
       SUM(s.Qty * (a.Price - a.Cost)) * sp.CommRate / 100.0 as Commission,
       SUM(s.Qty * (a.Price - a.Cost)) * (1 - sp.CommRate / 100.0) as NetProfit
FROM Sales s JOIN
     Salespeople sp
     ON sp.EmployeeID = s.EmployeeID JOIN
     Appliances a
     ON s.AppID = a.ID 
GROUP BY sp.EmployeeName sp.CommRate
ORDER BY NetProfit DESC;

Stored Procedure Select from 3 tables

1 Answers