Thanks for the info :) I might have typo in the original email because all of these cols do exist in my database, and this query does in fact run -- which I agree is a little odd. I did run it with both ClientIP and ClientDomain in the GROUP BY -- it ran either way (bug?).
I completely forgot about the HAVING clause, so I will give that a try. Thanks again :) Shawn -----Original Message----- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 12:40 AM To: [EMAIL PROTECTED]; Shawn Anderson Subject: Re: [sqlite] Question about expected query result?? At 11:05 PM -0400 5/13/04, Shawn Anderson wrote: >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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
