tags:

views:

32

answers:

5

Consider a query similar to:

 SELECT sum(EXPR) as total,
        sum(EXPR) as total2, 
        sum(total+total2) as grandtotal 
 FROM tablename

This comes up and says unknown column total in field list.

Is there anyway to reference the alias fields in a calculation without retyping the sum expression because sum(EXPR) on each side is very long.

+3  A: 
Lasse V. Karlsen
+1 for extreme thoroughness. Yes, the subquery needs to be aliased.
Fosco
+3  A: 
SELECT total, total2, total + total2 as grandtotal from (
 SELECT sum(EXPR) as total, 
        sum(EXPR) as total2,  
 FROM tablename 
) x
Fosco
A: 
SELECT
WITH Sums AS
(
 SELECT sum(EXPR) as total, 
        sum(EXPR) as total2
 FROM tablename 
)
SELECT SUM(sums);

for you tsql fans...

JGord
T-SQL In MySQL?
Lasse V. Karlsen
MySQL doesn't support the WITH clause: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql
OMG Ponies
@Lasse V. Karlsen: The WITH clause is not specifically TSQL - Oracle supported non-recursive WITH since 9i.
OMG Ponies
I don't care about WITH, the answer says "for you tsql fans".
Lasse V. Karlsen
I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
JGord
A: 

You could use variables:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

elmonty
A: 
SELECT @total1 := sum(EXPR), 
       @total2 := sum(EXPR),  
       @total1 + @total2 as grandtotal  
FROM tablename 
kunal