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

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.

Thank you for the help, Machete!

actually, the criterion in Machete's example belongs in the WHERE clause
a better example of a HAVING condition is: authors with 4 or more occurrences:

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!