distinct count

[Guest]

I want a count of States in Order and am thinking that something like "select
count(distinct state) from order;" might work, but it doesn't. I know that I
could do two queries with a "group by state" in the first and a count in the
second, but I would like to be able to do in one query. Any thoughts?

 

[John Vinson]

I want a count of States in Order and am thinking that something like "select
count(distinct state) from order;" might work, but it doesn't. I know that I
could do two queries with a "group by state" in the first and a count in the
second, but I would like to be able to do in one query. Any thoughts?

You can use two queries conflated into one with a Subquery, but you're
correct - Access does not support the ANSI-standard COUNT(DISTINCT....
syntax.

Try

SELECT Count(*) FROM
(SELECT DISTINCT State FROM Order);

John W. Vinson[MVP]

 

[Chris2]

I want a count of States in Order and am thinking that something like "select
count(distinct state) from order;" might work, but it doesn't. I know that I
could do two queries with a "group by state" in the first and a count in the
second, but I would like to be able to do in one query. Any

thoughts?

The following:

SELECT O1.State
,COUNT(O1.State)
FROM Order AS O1
GROUP BY O1.State

. . . seemed to work ok on my example Addresses table.


Sincerely,

Chris O.