Menu
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.
690
If mixed-case letters or special symbols, or spaces are required, quotes must be used. Column aliases can be used for derived columns. Column aliases can be used with GROUP BY and ORDER BY clauses. We cannot use a column alias with WHERE and HAVING clauses.
You can alias an expression or column, such as in select u.id as user_id and select upper(u. username) as uppercase_name , and you can alias a table as in from users u . There exists no group alias for multiple columns.
Advantages of MySQL AliasesIt makes the column or table name more readable. It is useful when you use the function in the query. It can also allow us to combines two or more columns. It is also useful when the column names are big or not readable.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
