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