finiteloop finiteloop - 7 days ago 6
SQL Question

SQL 4 tables inner join pick up sum Nulls also?

I have 4 tables.... Employees, Customers, Orders and Order_Info. I am trying to inner join the 4 tables to sum up the order amounts and calculate the employees commission based on 7%. I am very close to solving this but I have one slight problem: I am not getting all of the employees because less are showing up than exist in my Employees table. This is how I currently have my query written:

SELECT Employees.lName, Employees.fName,
SUM(quantOrdered * costEach) AS ttl_orders_value,
(SUM(quantOrdered * costEach) * .07) AS Commission
FROM Customers
INNER JOIN Employees ON Customers.empNumber = Employees.empNumber
INNER JOIN Orders ON Customers.custNumber = Orders.custNumber
INNER JOIN Order_Info ON Orders.ordNumber = OrderDetails.ordNumber
GROUP BY Employees.lName, Employees.fName
ORDER BY Employees.lName, Employees.fName


I wish to get all employees even if their commission and total sales equal zero, which I believe to be calculated from NULLS.

Any help improving my query would be greatly appreciated!

Answer

try this:

SELECT Employees.lName, Employees.fName,
       SUM(ISNULL(quantityOrdered,0) * ISNULL(priceEach,0)) AS ttl_orders_value,
       (SUM(ISNULL(quantOrdered,0) * ISNULL(costEach,0)) * .05) AS Commission
FROM Employees 
LEFT JOIN Customers ON Customers.empNumber = Employees.empNumber 
LEFT JOIN Orders 
   INNER JOIN OrderDetails ON Orders.ordNumber = OrderDetails.ordNumber
ON Customers.custNumber = Orders.custNumber
WHERE Employees.workTitle = 'Developer' 
GROUP BY Employees.lName, Employees.fName 
ORDER BY Employees.lName, Employees.fName

Note, I have changed INNER JOIN to LEFT JOIN only for Orders table because as you say Employees records should be there, they only may not have linked orders.

You may also need to wrap NULL priceEach, costEach, quantityOrdered and quantOrdered values with ISNULL([field_name], 0) to get proper results for those employees not having any orders.