81

Why won't this query work?

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

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

This is a simplified version of what I am trying to do, but basically I would like to use an alias to make other calculations. My calculations are much more complicated and thats why it would be nice to alias it since I repeat it several times different ways.

dreftymac
  • 31,404
  • 26
  • 119
  • 182
Tom Rossi
  • 11,604
  • 5
  • 65
  • 96

4 Answers4

195

Simply wrap your reused alias with (SELECT alias) :

SELECT 10 AS my_num, 
       (SELECT my_num) * 5 AS another_number
FROM table
zessx
  • 68,042
  • 28
  • 135
  • 158
  • 17
    I am wondering where that functionality is documented. I've searched in the whole MySQL documentation and I have not found it. I don't know how this "alias reusing" works and I am curious about portability or performance impact. If someone knows more about this I will appreciate a link. Thanks you. – Carlos Gant Jun 13 '14 at 16:55
  • 1
    this just made my week.. maybe month. super useful especially with aliases built from flow control statements like `IF()`, `CASE()`, etc – But those new buttons though.. Mar 09 '16 at 20:06
  • @CarlosGant: The `(SELECT alias)` part is treated as a "dependent subquery", at least according to MySQL's `EXPLAIN` and Visual Explain functions. – DCoder Jun 18 '16 at 18:31
  • 7
    Nevermind, it didn't work for me. I got "unknown column". – Captain Hypertext May 09 '17 at 14:21
  • `SELECT kabupaten.nama AS kabupaten, kecamatan.nama AS kecamatan, SUM(pihaks.luas) AS target_luas, SUM(realisasi.luas) AS realisasi_luas, SELECT(realisasi_luas)/SELECT(target_luas)*100 AS persentase_luas`. Tried like this but not working – Ray Coder Mar 01 '21 at 08:19
  • This doesn't work for me if I try to use `SELECT` multiple times. `SELECT Users.Id, SUM(Posts.Score) AS ScoreSum, SUM(Posts.ViewCount) AS ViewSum, (SELECT(ViewSum) / SELECT(ScoreSum)) AS ViewScoreRatio` says "Incorrect syntax near SELECT" for the second `SELECT`. – Aaron Franke Jun 16 '21 at 20:56
11

You'll need to use a subselect to use that aliases that way

SELECT my_num*5 AS another_number FROM
(
    SELECT 10 AS my_num FROM table
) x
Rubens Farias
  • 57,174
  • 8
  • 131
  • 162
6

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";
Community
  • 1
  • 1
Rob Van Dam
  • 7,812
  • 3
  • 31
  • 34
0

``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
Freddy Jose Chirinos Moròn
  • 306
  • 2
  • 8