Re: Column Alias Bug??
Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? As others have pointed out, your query requires a HAVING rather than a WHERE. What hasn't been pointed out, which is worth knowing, is that column aliases cannot be used in *any* WHERE clause. ...because the WHERE clause is processed first, and the SELECT evaluated later. Similarly GROUP BY is processed before HAVING (which is why that suggestion works). Somewhere there's a 'table' or list showing the sequence that the various clauses are evaluated/executed. Is it in the manual or in your book Paul? If it's online, it would be useful to know where so that a link can be added to answers to such questions... Anyone remember where? =dn - 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: Column Alias Bug??
Somewhere there's a 'table' or list showing the sequence that the various clauses are evaluated/executed. Is it in the manual or in your book Paul? If it's online, it would be useful to know where so that a link can be added to answers to such questions... Anyone remember where? This might do, it's in the SELECT syntax. It lists all the options and mentions that they must be in THAT ORDER. http://www.mysql.com/doc/S/E/SELECT.html -- Steve Rapaport still at large - 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: Column Alias Bug??
Steve, Thanks for this. I took another breeze through but didn't spot it - at least what I had in mind. Somewhere I've seen a short pithy summary/list, giving the sequence. Had a flip through the book too, but failed to spot it. Hence the request. Never mind, I'll spot in again one day, and then no one will ask such a question for a clear two weeks!!! Regards, =dn - Original Message - From: Steve Rapaport [EMAIL PROTECTED] To: DL Neil [EMAIL PROTECTED] Cc: Rick Emery [EMAIL PROTECTED]; [EMAIL PROTECTED]; Paul DuBois [EMAIL PROTECTED] Sent: 19 January 2002 21:18 Subject: Re: Column Alias Bug?? Somewhere there's a 'table' or list showing the sequence that the various clauses are evaluated/executed. Is it in the manual or in your book Paul? If it's online, it would be useful to know where so that a link can be added to answers to such questions... Anyone remember where? This might do, it's in the SELECT syntax. It lists all the options and mentions that they must be in THAT ORDER. http://www.mysql.com/doc/S/E/SELECT.html -- Steve Rapaport still at large - 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 - 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
Column Alias Bug??
Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? - 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: Column Alias Bug??
Hi, Try : SELECT a,COUNT(*) AS z FROM aa GROUP BY a HAVING z1; Regards, Jocelyn - Original Message - From: Rick Emery [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 10:30 PM Subject: Column Alias Bug?? Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? - 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 - 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: Column Alias Bug??
count(*) works if I do not alias it with z, and do not use WHERE clause: mysql select a,count(*) from aa group by a; +--+--+ | a| count(*) | +--+--+ |1 |2 | |2 |2 | |3 |2 | |4 |1 | |5 |1 | |6 |1 | +--+--+ 6 rows in set (0.40 sec) -Original Message- From: Nathan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 3:34 PM To: Rick Emery Subject: Re: Column Alias Bug?? What does the count(*) do? If that's failing, maybe the alias doesn't get created? Shot in the dark obviously... :-) # Nathan - Original Message - From: Rick Emery [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 2:30 PM Subject: Column Alias Bug?? Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? - 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 - 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: Column Alias Bug??
Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? Because you should be using HAVING rather than WHERE: select a,count(*) as z from aa group by a having z1; / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: Column Alias Bug??
At 15:30 -0600 1/18/02, Rick Emery wrote: Is this a bug? I can't find an answer to this question in FAQs or archives. CREATE TABLE aa ( a int); INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); the following : mysql select a,count(*) as z from aa where z1 group by a; displays this error: ERROR 1054: Unknown column 'z' in 'where clause' Why isn't z recognized as a column identifier? As others have pointed out, your query requires a HAVING rather than a WHERE. What hasn't been pointed out, which is worth knowing, is that column aliases cannot be used in *any* WHERE clause. - 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
