CODEWITHSUNDEEP
sql
user2328819
user2328819

Reputation: 75

how to get value x without code duplication


    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

Upvotes: 5

Views: 67

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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

Upvotes: 1

Jeremy Zhou
Jeremy Zhou

Reputation: 49

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.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

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

Upvotes: 5

Related Questions