how to get value x without code duplication

5
votes

    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
3
sql
1) The answer may vary based on your SQL vendor. MySQL? Oracle? SQL Server? 2) I bet a temp table holding the first two columns, then a select from it to select the two columns plus the new column, could do it - Patashu

3 Answers

5
votes

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
1
votes

Also in SQLServer2005+ you can use CTE

;WITH cte AS
 (
  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
  )
  SELECT q, c, q + c as x
  FROM cte
0
votes

You can't do that unfortunately.

The ALIAS can not be used in the same level where you created them.

A temporary table is necessary, i think.