Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Unanswered: Counting Distinct

    Not sure why this won't work, but I'm getting a missing operator error.

    I have a table, say tblFruit with the following entries in column Fruit:
    Apple
    Apple
    Apple
    Pear
    Pear
    Orange

    I want to know that there are three distinct entries in this table, but don't want to know what they are.

    I tried: SELECT COUNT(DISTINCT Fruit) FROM tblFruit;

    But the missing operator quotes COUNT(DISTINCT Fruit) as being the menace to my needs.

    Does anyone know how to accomplish what I am looking for?


    TIA
    All code ADO/ADOX unless otherwise specified.
    Mike.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Not sure exactly what you are after, but try this

    SELECT Count(Table1.fruit) AS CountOffruit
    FROM Table1
    GROUP BY Table1.fruit;

    S-

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    This would return
    3
    2
    1

    And I apologise as after review, I didn't actually specify what I am asking for.

    I want the number 3 to be returned because there are three different types of fruits.

    TAIA
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Mike,

    Try this:

    SELECT TOP 1 Count(*) AS Expr1
    FROM Fruit
    GROUP BY Fruit.fruit;

    I tested it and it returned 3. I'm not certain it will work with other test beds ...

    Heck, it probably isn't even returning the right count ...

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nope. It doesn't work ... Added a few more fruits and still returns 3 ... It returns the 1st count in the list ...

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DOH! I hate when I get a solution 2 minutes later ....

    Try this:

    SELECT Count(*) AS Expr1
    FROM [SELECT DISTINCT Fruit.fruit FROM Fruit]. AS [%$##@_Alias];


    ... And no, it's not swearing.

  7. #7
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Beautiful. Works perfectly. I guess I've been so turned off by the limited subselects you can use, I just could not think of this simple solution.

    Anyone viewing this: This is useful when you want to calculate things like dynamically resizing the ListRows of a combo box, or quick numbers validation between two tables, etc.

    Thanks, M Owen, for the good solution.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •