Combining GROUP BY and JOIN

Question

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the concept. I'm using a MS Access .mdb database, and running the query in MS Access 2010.

I need to SUM BalanceDue and GROUP BY CustomerID, then I need to replace the CustomerID with the Company name from the Customers table.

Orders table:

OrderNumber	CustomerID	BalanceDue
1000	1	250.00
1001	2	100.00
1002	2	50.00
1003	3	100.00
1004	1	200.00

Customers table:

CustomerID	Company
1	ABC Inc
2	XYZ Inc
3	Widgets LLC

And the expected result:

Company	Total
ABC Inc	450.00
XYZ Inc	150.00
Widgets LLC	100.00

Here's the query I came up with, but the results don't appear to be accurate:

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

To check the accuracy, I just ran the following "simple" query (one I fully understand/trust):

SELECT Orders.CustomerID, Sum(Orders.BalanceDue) AS Total
FROM Orders
GROUP BY Orders.CustomerID;

When I compare the results from the two queries above, the results don't match. The first query has less rows, so it must be "skipping" some customers?

What am I doing wrong?

Answer 1

It also depends on what the end result needs to be.

If you want "Balance due per Customer", then you have the right query.

If you want "How much money do we still have to collect overall", then you need to also grab the money where there are no CustomerID, or where the CustomerID is not in the Company table. (Theses can be over-the-counter customers in a small store, for example).

So, try this: add a new row in Orders

INSERT INTO Orders (OrderNumber, CustomerID, BalanceDue) VALUES (1005, 5,999)

Now change your "INNER JOIN" for a "LEFT JOIN". This will get you all the values of the left table, even if there is no corresponding record in the right table.

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

You will see that you have 999$ assigned to the Customer "NULL". But at least you have all the money listed.

As with alot of things SQL and Reporting: it's all a matter of knowing exactly what you are looking for. Keep exploring, and keep asking questions.

Answer 2

select e.EmpCode,e.FirstName as employeename,m.EmpCode as managercode,m.FirstName as managername, p.ProjectName
from [dbo].[TblEmployee] e inner join [dbo].[TblEmployee] M on m.EmpId =e.ManagerId full join [dbo].[TblProject] p on e.EmpId = p.ProjectId