MYSQL - Unknown column 'a' in 'field list'

Question

When I am running a query like that it works perfectly:

SELECT 10 as a;

But when I am using the column a

SELECT 10 as a, (a - 1) as b

I get following error: Unknown column 'a' in 'field list'

Why isn't it working that way?

Answer 1

You can't reuse an alias in the same SELECT statement in which it was defined. Perhaps the closest thing to what you want to do here would be this:

SELECT
    t.a,
    (t.a - 1) AS b
FROM (SELECT 10 AS a FROM dual) t;

Answer 2

You could also be using user variables.

SET @a := 10;
SELECT
     @a AS a
  , (@a - 1) AS b
FROM DUAL;

Rewritten to one query

SELECT
     @a AS a
  , (@a - 1) AS b
FROM
 (SELECT @a := 10) AS init_user_param ;

More optimal

SELECT 10 AS a , 9 AS b FROM DUAL;

Answer 3

You can try like below.

select t1.a,(t1.a-1) as b from  (SELECT 10 as a) t1;