SQL Count(Distinct.....)

[goterps1]

I'm recieving a missing operator error on the select statment when attempting to run the following sql. Can you help?

SELECT COUNT(DISTINCT [Fund 49].SSN) AS Expr1 INTO [Matching 49]
FROM [Fund 49], [Fund 83]
WHERE ((([Fund 49].SSN)=[Fund 83].SSN));

 

[PHV]

JetSQL lacks the Count(DISTINCT ...) syntax.
A workaround:
SELECT Count(*) AS DistinctCount
INTO [Matching 49] FROM (
SELECT DISTINCT A.SSN FROM [Fund 49] AS A INNER JOIN [Fund 83] AS B ON A.SSN = B.SSN
) AS D

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goterps1]

Great it works! Thanks!