Re-use a result field into a query
Hi guys. I´ve got a low-priority, non-important newbie question: Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: Select a.field1, (a.field2+a.field3) AS result1, (result1*100) AS result2 #Here is the question. FROM table1 AS a GROUP BY a.field1 I know I can get the desired result with the following query Select a.field1, (a.field2+a.field3) AS result1, ((a.field2+a.field3)*100) AS result2 FROM table1 AS a GROUP BY a.field1 but, sometimes I have quite big calculations into a query a I need to use them quite often. I used to do that in MSAccess (because it is possible ot save queries). Is it possible or there is a way to re-use results from a query in MySql?. Thanks guys and very best regards. PD: Anyway, I do love my transition from MSAccess to MySql. My system is: MySql 4.1.10 into a Debian Sarge Box.
Re: Re-use a result field into a query
From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-use a result field into a query
Well Alvaro, you can use variables to re-use resultas like that : mysql set @rs=0; Query OK, 0 rows affected (0.02 sec) mysql select 1, (@rs:=2+3) as result1, - (@rs*100) as result2 - from dual; +---+-+-+ | 1 | result1 | result2 | +---+-+-+ | 1 | 5 | 500 | +---+-+-+ 1 row in set (0.00 sec) Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-use a result field into a query
Thanks guys for your quick and clear help: It gives me elements to research. I think I'll try the variable approach, and the way in how to use it with PHP. Thanks again and best regards, Alvaro. - Original Message - From: [EMAIL PROTECTED] To: Jigal van Hemert [EMAIL PROTECTED] Cc: Alvaro Cobo [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, June 12, 2005 1:11 PM Subject: Re: Re-use a result field into a query Well Alvaro, you can use variables to re-use resultas like that : mysql set @rs=0; Query OK, 0 rows affected (0.02 sec) mysql select 1, (@rs:=2+3) as result1, - (@rs*100) as result2 - from dual; +---+-+-+ | 1 | result1 | result2 | +---+-+-+ | 1 | 5 | 500 | +---+-+-+ 1 row in set (0.00 sec) Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
