Re: select stmt problem
Teddy A Jasin writes: Hi, I have this mysql statement: 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 and i Get this following error: Unknown column 'counts' in 'where clause' why is that so??? it works before and now give me this problem. If that worked before, something was very wrong. If you take a look at how WHERE and GROUP BY work, you'll see that the grouping is done _after_ it's known what rows there are to group and what they should be grouped on. What you probably want to use is HAVING for the 'counts = 10' part. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
Re: select stmt problem
for one, counts is not a colums, it aggreate values. In your case, since you didn't group, all record retrieved will have the same value for count(*), the number of record. So of course you can't order by counts, it's a single value. On Wednesday 24 October 2001 02:43 am, Carl Troein wrote: Teddy A Jasin writes: Hi, I have this mysql statement: 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 - 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
Re: select stmt problem
I did the grouping too... ...group by hpnumber so what could be wrong? regards, Teddy At 02:13 AM 10/24/2001 -0400, Kodrik wrote: for one, counts is not a colums, it aggreate values. In your case, since you didn't group, all record retrieved will have the same value for count(*), the number of record. So of course you can't order by counts, it's a single value. On Wednesday 24 October 2001 02:43 am, Carl Troein wrote: Teddy A Jasin writes: Hi, I have this mysql statement: 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 - 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 Teddy A Jasin Systems Analyst / Administrator 45B, Circular Road Singapore 049400 Tel: 438 2418 DID: 4386924 Fax: 438 4803 email: [EMAIL PROTECTED] w w w . e f u s i o n . c o m . s g - 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
Re: select stmt problem
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
RE: select stmt problem
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
select stmt problem
Hi, I have this mysql statement: 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 and i Get this following error: Unknown column 'counts' in 'where clause' why is that so??? it works before and now give me this problem. any idea? regards. Teddy - 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
