Will Smyth - 1 year ago 78
SQL Question

# Stored Procedure Select from 3 tables

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
``````

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;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download