Syntax Error with DISTINCT 2

[tlove161]

I am trying to get a distinct count of LAW_NM from the LAW_AG table. I keep getting a syntax error. Here is the SQL for the count. Where do I add the DISTINCT keyword to get a unique count of LAW_NM?

SELECT Count(LAW_AG.LAW_NM) AS CountOfLAW_NM
FROM [ALL] INNER JOIN LAW_AG ON [ALL].EVENT_ID = LAW_AG.EVENT_ID
WHERE ((([ALL].EVENT_TYPE)=0) AND (([ALL].DATE) Between #8/19/2005# And #9/5/2005#));

Thanks,
Troy

 

[PHV]

JetSQL lacks the Count(Distinct ...) aggregate function.
One work around is to play with an inline view:
SELECT Count(*) AS CountOfLAW_NM
FROM (
SELECT DISTINCT LAW_AG.LAW_NM
FROM [ALL] INNER JOIN LAW_AG ON [ALL].EVENT_ID = LAW_AG.EVENT_ID
WHERE [ALL].EVENT_TYPE = 0 AND [ALL].DATE Between #2005-08-19# And #2005-09-05#
) 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

 

[tlove161]

PHV-

Thanks, it works now! What does the "As D" at the end of the expression mean?

Troy

 

[PHV]

It is an alias to give a name to the embedded view.

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