Using an Alias in SQL Calculations
Aliases in sql are not like variables in a programming language. Aliases can only be referenced again at certain points (particularly in GROUP BY
and HAVING
clauses). But you can't reuse an alias in the SELECT
clause. So you can use a derived query (such as suggested by Rubens Farias) which lets you basically rename your columns and/or name any computed columns.
Or you could use a VIEW
if your formulas are generally fixed
CREATE VIEW table10 AS SELECT 10 AS my_num FROM table;SELECT my_num * 5 AS another_number FROM table10;
I believe that will be slightly faster than using a derived query but it probably depends a lot on your real query.
Or you could just duplicate the work:
SELECT 10 AS my_num, 10 * 5 AS another_number FROM table;
Which might be convenient in something like php/perl:
my $my_num = 10;my $query = "SELECT $my_num AS my_num, $my_num * 5 AS another_number FROM table";