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!
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;