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.
Here's the order of how things are executed in a database engine.
Note that this is a semantic view of how things are executed, the database might do things in a different order, but it has to produce results as though it was done this way.
Some database engines allows you to circumvent this though, by saing "GROUP BY 2" to group by the 2nd column in the SELECT-part, but if you stick to the above order, you should know by now that the reason that your code doesn't work is that there are no columns with the names total or total2 (yet).
In other words, you need to either repeat the two expressions, or find another way of doing it.
What you can do is to use a sub-query (providing you're on a MySQL version that supports this):
SELECT total, total2, total+total2 as grandtotal
FROM (
SELECT sum(EXPR) as total, sum(EXPR) as total2
FROM tablename
) x
Striking out the rest as per the comment.
I don't know much about MySQL though so you might have to alias the sub-query:
...
FROM tablename
) AS x
^-+^
|
+-- add this
Some database engines also disallow using the keyword AS when aliasing subqueries, so if the above doesn't work, try this:
...
FROM tablename
) x
^
|
+-- add this
SELECT total, total2, total + total2 as grandtotal from (
SELECT sum(EXPR) as total,
sum(EXPR) as total2,
FROM tablename
) x
SELECT
WITH Sums AS
(
SELECT sum(EXPR) as total,
sum(EXPR) as total2
FROM tablename
)
SELECT SUM(sums);
for you tsql fans...
