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?

Philippe · Answer

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.

Combining GROUP BY and JOIN

One Answer

Add your own answers!

Ask a Question