Using an alias in SQL calculations

Question

Why does this query not work?

SELECT 10 AS my_num, my_num*5 AS another_number FROM table 

In this example, I am trying to use the alias my_num in other calculations. This results in an unknown column "my_num"

This is a simplified version of what I'm trying to do, but basically I would like to use an alias for other calculations. My calculations are much more complicated, and therefore it would be nice if he repeated this several times in different ways.

Answer 1

Just add a reused alias (SELECT alias) :

 SELECT 10 AS my_num, (SELECT my_num) * 5 AS another_number FROM table 

Answer 2

You will need to use a subtitle to use these aliases in this way

 SELECT my_num*5 AS another_number FROM ( SELECT 10 AS my_num FROM table ) x 

Answer 3

Aliases in sql are not variables in a programming language. Aliases can only be specified at specific points (especially in GROUP BY and HAVING ). But you cannot reuse an alias in a SELECT . That way, you can use a derived query (like the one suggested by Rubens Farias ), which allows you to basically rename your columns and / or specify any calculated columns.

Or you can use VIEW if your formulas are usually fixed.

 CREATE VIEW table10 AS SELECT 10 AS my_num FROM table; SELECT my_num * 5 AS another_number FROM table10; 

I believe this will be a little faster than using a derived query, but it probably depends a lot on your real request.

Or you can just duplicate the work:

 SELECT 10 AS my_num, 10 * 5 AS another_number FROM table; 

What 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"; 

Answer 4

`` Another option is to use the APPLY operator

 SELECT my_num, my_num*5 AS another_number FROM table CROSS APPLY (SELECT 5 AS my_num) X