column aliases

[captlid]

say you got:
select sum(col1) as col2, sum(col3) as col4, (col2+col4)/col2 as answer from table

Is it possible to do mathematical operations on aliased columns?

Havent found much in the manual about it.

 

[cLFlaVA]

why not:

select sum( col1 ) as col2
     , sum( col3 ) as col4
     , ( sum( col1 ) + sum( col3 ) ) / sum( col1 ) as answer
  from table

*cLFlaVA
----------------------------
[tt]a frickin' twelve-gauge, what do you think?[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[captlid]

because the calculations are being done on derived results.

 

[ericbrunson]

Did you try it?

foxtrot(~)$ date
Wed Apr 13 22:19:37 MDT 2005
foxtrot(~)$ mysql -p -h s1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8037 to server version: 4.0.17-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;
ERROR 1007 (00000): Can't create database 'test'. Database exists
mysql> create table sums ( col1 integer, col2 integer );
ERROR 1046 (00000): No Database Selected
mysql> use test;
Database changed
mysql> create table sums ( col1 integer, col2 integer );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into sums values ( 1, 2 );
Query OK, 1 row affected (0.06 sec)

mysql> insert into sums values ( 3, 4 );
Query OK, 1 row affected (0.06 sec)

mysql> insert into sums values ( 5, 6 );
Query OK, 1 row affected (0.05 sec)

mysql> select sum( col1 ), sum( col2 ) from sums;
+-------------+-------------+
| sum( col1 ) | sum( col2 ) |
+-------------+-------------+
|           9 |          12 |
+-------------+-------------+
1 row in set (0.06 sec)

mysql> select sum( col1 ) as sum1, sum( col2 ) as sum2 from sums;
+------+------+
| sum1 | sum2 |
+------+------+
|    9 |   12 |
+------+------+
1 row in set (0.05 sec)

mysql> select sum( col1 ) as sum1, sum( col2 ) as sum2, sum1 + sum2 from sums;
[solution left as an exercise to the student]
mysql> exit;
Bye
foxtrot(~)$ date
Wed Apr 13 22:22:22 MDT 2005
foxtrot(~)$

Two minutes and 45 seconds.

It probably took you longer to post your question than it would have to try it yourself.

 

[captlid]

i did try it out myself I put it up there because it was easier to show that then to make a huge explanation. I know it doesnt work. I wanted to know if something like that can be done.