When I run the following query, I get back 0 results -- anyone have any thoughts? I am expecting to get back 3 records.
SELECT DISTINCT ClientIP, ClientDomain, COUNT(ClientDomain) AS ClientDomainCount FROM SMTPLog WHERE ClientDomainCount > 1 GROUP BY ClientIP ORDER BY ClientDomainCount DESC, ClientDomain ASC
Actually, I'm surprised that you didn't get a parse/syntax error with that query, such as for referencing columns that don't exist.
In any event, the problem is about order of execution. Expressions in the SELECT list are evaluated after the GROUP BY clause is evaluated, which is after FROM and WHERE are evaluated.
If you want to filter your output based on the results of a group function, you need to put the search condition in the HAVING clause. HAVING looks like WHERE but it runs after GROUP BY.
Moreover, if you want to use a field in the SELECT list when you have a GROUP BY clause, then that field has to be in the GROUP BY list. Its an error if you don't. And COUNT doesn't take any arguments anyway, besides '*', as they don't make sense.
So try this:
SELECT ClientIP, ClientDomain, COUNT(*) AS ClientDomainCount FROM SMTPLog GROUP BY ClientIP, ClientDomain HAVING ClientDomainCount > 1 ORDER BY ClientDomainCount DESC, ClientDomain ASC
Does that work?
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
