Count Distinct 1

[anna223]

Most of my SQL background is with Oracle DBMS' but need to write an MSAccess query that counts the distinct occurances of a value within a table. I can't find the correct syntex for such a query command in MSAccess.

Example: An order detail table has multiple rows per order - detailing each item within the order. I want to count the number of distinct orders, not the number of items. In oracle I would say:

count(distinct(order_id)) from order_detail_table

Thanks!!

 

[PHV]

JetSQL lacks the Count(Distinct ...) aggregate function.
A workaround is to play with a subquery:
SELECT D.SomeFields, Count(*) AS CountOfOrders
FROM (SELECT DISTINCT A.SomeFields, A.order_id FROM order_detail_table A
) AS D
GROUP BY D.SomeFields;


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

 

[spizotfl]

thanks phv, this sure helped...