# Multiple calculations in a select statement with CASE

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?

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.

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
``````
• 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"). Nov 7, 2016 at 9:30
• No space, please. Thanks in advance. Nov 7, 2016 at 17:20