Distinct Count with two tables

I have two tables on Access, Customer and Transaction. I'm trying to find the total # of transactions sorted by Dogs (0-3). The Transaction table has a line for each item bought, so multiple lines can be for one TransactionID.

Here's what I have so far:

SELECT Customer.Dogs, COUNT(Transaction.TransactionID) AS TotTrans
FROM Transaction, Customer
WHERE Transaction.CustomerID = Customer.CustomerID
GROUP BY Dogs

And I get

Dogs | TotTrans
0    | 130104
1    | 59132
2    | 17811
3    | 1401

Obviously this counts the total rows in the Transaction Table and sorts them by # of dogs. However, it is counting for the duplicates in the Transaction Table (e.g. There are three rows with TransactionID = 2, because in that transaction the customer bought 3 items. The Count is obviously including the extra 2 rows).

When I try to do COUNT(DISTINCT Transaction.TransactionID) it doesn't work, and the message

"Syntax error (missing operator) in query expression 'COUNT(DISTINCT Transaction.TransactionID)'.

I have looked around, but can't seem to find the solution. I think part of the problem stems from the fact that I'm selecting two attributes.

If anyone could help explain what to do and the logic behind it, that would be great!

Answers


You should join the customer table with an already distinct-ed table (using inner query)

SELECT Customer.Dogs, COUNT(distinctTransactions.TransactionID) AS TotTrans
FROM (select distinct TransactionID,CustomerID from Transaction) as 
distinctTransactions, Customer
WHERE distinctTransactions.CustomerID = Customer.CustomerID
GROUP BY Dogs

You should learn to use proper join syntax. Also, table aliases make the query easier to write and to read:

SELECT c.Dogs, COUNT(DISTINCT t.TransactionID) AS TotTrans
FROM Transaction t JOIN
     Customer c
     ON t.CustomerID = c.CustomerID
GROUP BY c.Dogs
ORDER BY c.Dogs;

Need Your Help

Accessing request object from form.save

django django-forms

I'm using the pyfacebook module for Django to implement facebook Connect for one of my sites, I'm not using a local User object because I only want to use Facebook for login.

Exit from doing something every x seconds

java android

Hello everyone I'm trying to stop a method from doing something every 6 seconds once a certain criteria is met. I figured to use finish() but it redirects me back to the previous