distinct count

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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]
 
Stukmeister said:
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
Click to expand...
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.
 
Back
Top