[AccessD] A2000: Am I blind? (swe)

Hi Steve

You can use a subquery as a subtable:

  SELECT DISTINCT
    State
  FROM 
    Addresses

Then:

  SELECT 
    COUNT(*) As StateCount
  FROM
      [SELECT DISTINCT
        State
      FROM 
        Addresses]. AS 
    T;

/gustav

>>> erbachs at gmail.com 12-02-2005 13:34:43 >>>
Dear Group,

Of course, I can do this with TWO queries. I'm just wondering if
there's something vital I'm missing that can cause it to be done in
one.

I have address data in a table. Many different states. I want to COUNT
the number of unique states. That is, if I have 5,000 addresses I want
to see how many states they come from. If I do something like this:

SELECT Count(State) AS CountOfState FROM Addresses

I get the number 5,000. Making it SELECT DISTINCT doesn't work. If I do
this:

SELECT State, Count(State) AS CountOfState FROM Addresses GROUP BY
State

I get a list of the states with how many occurrences there are for
each. But can I not use one query to get me the count of all the
different states there are?

Simon says, you may laugh.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com