SQL Question
SQL - cannot perform distinct count when joining two tables
I have two tables: Customers and Orders. I do a left join of Customers and Orders on CustomerID column which is primary key in Customers and foreign key in Orders.
- When I list CustomerID after joining, I get the list as expected.
- When I count the number of CustomerID, again I get the number of record I am expecting.
- When I use distinct count for CustomerID, i get an error.
1.
select Customers.CustomerID as list
from Customers left join Orders on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID = 4;
2.
select count(Customers.CustomerID) as numRecord
from Customers left join Orders on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID = 4;
3.
select count(distinct (Customers.CustomerID)) as numRecord
from Customers left join Orders on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID = 4;
I cannot understand where is the error. Any help would be appreciated.
The error:
Error in SQL:
Syntax error (missing operator) in query expression 'count(distinct Customers.CustomerID)'.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
You don't need the () around Customers.CustomerID in count(distinct ..)
select count(distinct Customers.CustomerID) as numRecord
from Customers
left join Orders on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID = 4;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download