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

Gustav Brock Gustav at cactus.dk
Sat Feb 12 07:23:04 CST 2005


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 




More information about the AccessD mailing list