Select Distinct Count 2

[dmlatham]

I would like to get a row count but I want to only count the distinct items in that row. For instance:

Fruit
-------
apples
apples
oranges
grapes
oranges
oranges

If I do a 'select distinct fruit from supermarket', I will of course return

Fruit
-----
apples
grapes
oranges

I would like to do a 'select count distinct fruit' and have an outcome of '3' to represent that I have 3 distinct fruits. How do I perform this query in ACCESS when ACCESS does not support 'select count distinct'? Your help is appreciated.

 

[THoey]

How about a subquery:

SELECT COUNT(*)
FROM MyTable
WHERE MyTable.Fruit IN
(
SELECT DISTINCT FRUIT
FROM MyTable
)

I didn't test this, but it should be close... Terry M. Hoey

 

[dmlatham]

Thank you for your response, but that did not work. If you run the 'select distinct' part of the subquery by itself it will return the 3 items, but if you add the 'select count' and make a subquery you get "6".

 

[THoey]

I think I have also seen:

SELECT COUNT(SELECT DISTINCT FRUIT FROM MyTable)
FROM MyTable
WHERE MyTable.Fruit IN

but I am not sure... (Sorry, don't have time to build a test DB)
Terry M. Hoey

 

[dmlatham]

I just tried that and it didn't work.

 

[Sawatzky]

dear dmlatham

try this:

SELECT Count(fruits.fruit) AS [number of fruit], fruits.fruit
FROM fruits
GROUP BY fruits.fruit;

regards astrid

 

[dmlatham]

I just tried that and my results were a list of numbers. I don't understand why there isn't a "select count distinct" in Microsoft Access or a way to manipulate the select statements with subquery to return total of unique values.

 

[dmlatham]

I forgot to mention that if I do a

select count (distinct fruit)
from supermarket

I get the correct amount.

 

[dmlatham]

My mistake. I meant, I use that statement in SQL Server the amount is correct. I don't see a way to use that same statement in Microsoft Access.

 

[Sawatzky]

dear dmlathan,

i did not read your question carefully enough, sorry for that.

try this:
SELECT count( f ) from (select First(fruits.fruit) AS f
FROM fruits
GROUP BY fruits.fruit);

I hope I got it now.

regards astrid.