On Wednesday 24 October 2001 05:43 am, you wrote: > I did the grouping too... "...group by hpnumber" > > so what could be wrong? > > > > > select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs > > > > where counts > 10 and datesent between '2001-09-24' and '2001-10-24' > > > > and (returncode > 0 and returncode < 10) group by hpnumber order by > > > > counts DESC
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. --------------------------------------------------------------------- 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
