how to get value x without code duplication

sql

Solution

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

Problem

``` 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 ```

Original source