how to get value x without code duplication

Question

    create table t(a int, b int);
    insert into t values (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3);

    select * from t;

    a   |   b
    ----------
    1   |   1
    1   |   2
    1   |   3
    2   |   1
    2   |   2
    2   |   3
    3   |   1
    3   |   2
    3   |   3

    select
      max(case when a = 1 then b else 0 end) as q,
      max(case when b = 1 then a else 0 end) as c,
      (
        max(case when a = 1 then b else 0 end)
        +
        max(case when b = 1 then a else 0 end)
      ) as x
    from t

Is it possible to do something like this?

    select
      max(case when a = 1 then b else 0 end) as q,
      max(case when b = 1 then a else 0 end) as c,
      (q + c) as x
    from t

Answer 1

You can't use the ALIAS that was given on the same level of the SELECT clause.

You have two choices:

• by using the expression directly

query:

select
  max(case when a = 1 then b else 0 end) as q,
  max(case when b = 1 then a else 0 end) as c,
  (max(case when a = 1 then b else 0 end) + max(case when b = 1 then a else 0 end)) as x
from t

• by wrapping in a subquery

query:

SELECT  q, 
        c,
        q + c as x
FROM
(
  select
      max(case when a = 1 then b else 0 end) as q,
      max(case when b = 1 then a else 0 end) as c
    from t
) d