prog prog - 3 years ago 121
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.


  1. When I list CustomerID after joining, I get the list as expected.

  2. When I count the number of CustomerID, again I get the number of record I am expecting.

  3. 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)'.

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