2

I have the following query:

select
CASE
    WHEN f.nature = 'box'
    THEN 1
    WHEN f.nature = 'bag'
    THEN 3
    ELSE 0
END AS Pnature,
CASE
    WHEN f.type = 'electronics'
    THEN 0
    WHEN f.type = 'cloths'
    THEN 3
    ELSE 0
END AS Ptype
from product f

Now I want somehow to sum Pnature+Ptype in the select statement and do another CASE i.e. if Pnature+Ptype<100 then 20.

Is there a way to do this?

2 Answers 2

5

Using a derived table, as already shown, is one way. Another is to use CROSS APPLY:

SELECT
  CASE
    WHEN x.Pnature + x.Ptype < 100 THEN 20
    ELSE ...
  END,
  ...
FROM
  dbo.product AS f
  CROSS APPLY
  (
    SELECT
      CASE
        WHEN f.nature = 'box' THEN 1
        WHEN f.nature = 'bag' THEN 3
        ELSE 0
      END,
      CASE
        WHEN f.type = 'electronics' THEN 0
        WHEN f.type = 'cloths'      THEN 3
        ELSE 0
      END
  ) AS x (Pnature, Ptype)
;

The two method are somewhat similar: in both cases you are defining the expressions in a nested SELECT. The APPLY method is different in that you are not nesting the main query, only the bits that you want to additionally reference in the main SELECT.

4

Like that ?

select  case 
            when sum(Pnature+Ptype) < 100 then 20 
            else <something_else> 
        end
from (
    select 
    CASE f.nature
        WHEN 'box' THEN 1
        WHEN 'bag' THEN 3
        ELSE 0
    END AS Pnature,
    CASE f.type
        WHEN 'electronics' THEN 0
        WHEN 'cloths' THEN 3
        ELSE 0
    END AS Ptype
    from product f
    ) t
2
  • 1
    I don't think they meant to use the sum() function, though. They likely used the word "sum" in the sense "calculate the sum of" (but not "the total of").
    – Andriy M
    Nov 7, 2016 at 9:30
  • No space, please. Thanks in advance. Nov 7, 2016 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.