Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    South Africa
    Posts
    6

    Unanswered: Count Distinct count MADNESS!

    Hi

    I would like to ask since i cant find this anywhere on any forum.

    Currently I have a query that selects Distinct from a table.
    I need it to return those DISTINCT values aswell as how many times they do occer.
    After some searching i found this:
    strSQL = "SELECT COUNT(SHOWHORSEID) AS TOTAL FROM (SELECT DISTINCT SHOWHORSEID FROM SHOWFILES WHERE SHOWHORSECLASSID =" & strcnt &")" & ""

    However the above query ONLY returns how many Unique records there are and NOT HOW MANY TIMES EACH UNIQUE record occurs.

    At the moment i can only do this by running another seperate query during the "DO while recordset.eof" thus putting alot of stress on the connection and slowing my page down (even more than it aleady is!)

    Pleeeeaaase help

  2. #2
    Join Date
    Feb 2004
    Location
    Wicklow, Ireland
    Posts
    2

    Cool Use a grouped query instead

    HI,

    The solution is not to use a distinct query but to use a 'GROUP BY' clause.

    Try something like the following:

    SELECT AuthorID, COUNT(AuthorID) AS CountOfAuthorID
    FROM Authors
    GROUP BY AuthorID

    If you need to specify select criteria, add in a HAVING clause as follows:

    SELECT AuthorID, COUNT(AuthorID) AS CountOfAuthorID
    FROM Authors
    GROUP BY AuthorID
    HAVING AuthorID < 20


    Hope that helps.

    Machete.
    Last edited by Machete; 02-24-04 at 13:19.

  3. #3
    Join Date
    Feb 2004
    Location
    South Africa
    Posts
    6

    Ty

    Thank you for the help, Machete!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the criterion in Machete's example belongs in the WHERE clause
    PHP Code:
    select AuthorID
         
    count(AuthorID) AS CountOfAuthorID
      from Authors
     where AuthorID 
    20
    group 
        by AuthorID 
    a better example of a HAVING condition is: authors with 4 or more occurrences:
    PHP Code:
    select AuthorID
         
    count(AuthorID) AS CountOfAuthorID
      from Authors
    group 
        by AuthorID
    having count
    (AuthorID) >= 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Wicklow, Ireland
    Posts
    2

    Nice one.

    Yep - my mistake. Thanks for putting that right.


    Sure, the example I gave would have worked but it's not the right way to do it. A select criterion based on the author's ID code would definitely belong in the pre-grouped section of the query - under a WHERE clause.

    In fact, the example I gave was pretty lame and would make no sense whatsoever in a properly normalised database wherein an author's ID code would never appear more than once in the Author table.

    A better example would be as follows:
    Picture the database behind an online forum such as dBForums. There would likely be a table called 'Thread' in which posts made by various dBForum members or 'authors' would be persisted - one record per post.

    Let's say you want to retrieve a list of authors that have posted and the number of posts each has made, but only those authors with an ID code less than 20 and of those, only those that have posted more than 50 times. Finally, you want to order the dataset so that the most prolific posters are at the top.

    SELECT Author_ID, Count(Author_ID) As NumberOfPosts
    FROM Thread
    WHERE Author_ID < 20
    GROUP BY Author_ID
    HAVING Count( Author_ID ) > 50
    ORDER BY NumberOfPosts DESC


    Lesson learnt: Never try to write a technical forum reply when Larry's in the office standing behind you talking about Golf at the height of his voice!

Posting Permissions

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