> Well, count(*) is not a column, it is a function of a column. > When it searches, it doesn't know the result of count so you > cannot specify > it in the where clause. > > This would work, but you have all the records retrieved, not only > the ones > who have more than 10 in the group: > > select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs > where datesent between '2001-09-24' and '2001-10-24' > and (returncode > 0 and returncode < 10) group by hpnumber order by > counts DESC > > To do what you want, you would need to do a subquery. That's where > PostgresSQL has an advantage over it. > > Right now, you need to do two steps: > Get all the records with the counts associated to them. > When processing the data, ignores those having a count under 10. >
Usually, when somebody suggests that you need a subquery in a SELECT statement, they're just plain wrong. In my experience, I've never run across a real-world nested SELECT that couldn't be expressed a different (and more efficient) way. For deletes and updates, the nested SELECT's are much more useful. In this case, you need to put your clause "counts > 10" in a HAVING clause. Try this: select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs where datesent between '2001-09-24' and '2001-10-24' and (returncode > 0 and returncode < 10) group by hpnumber HAVING counts > 10 order by counts DESC Steve Meyers --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
