SQL If "something" select count

Question

The output that comes from a custom view of mine is as follows...

Col1    Col2    Col3   Col4

xxxx    Cake    1      1*
Cake    xxxx    2*     1
xxxx    Cake    2      0*
xxxx    Cake    0      0*
Cake    xxxx    2*     0
Cake    xxxx    2*     0

and what i would like to sum is...

For every row, 
if the word Cake is found in Col1, then add the value of Col3 to Sum
else add the value of Col4 to sum

The resulted SUM from the view above, should be 1 + 2 + 0 + 0 + 2 + 2 = 7

Thanks in advance!

p.s. The Asterisks are added just to show, which numbers should be added to sum.

Answer 1

Something along the lines of

select
   SUM( 
     Case 
         when Col1 = 'Cake' then Col3 
         when Col2 = 'Cake' then Col4 
         else 0 END 
   ) as MySum
from TheView

Answer 2

create table #sample
(
    Col1 varchar(50),
    Col2 varchar(50),
    Col3 int,
    Col4 int
)

insert into #sample VALUES ('xxxx', 'Cake', 1, 1);
insert into #sample VALUES ('Cake', 'xxxx', 2, 1);
insert into #sample VALUES ('xxxx', 'Cake', 2, 0);
insert into #sample VALUES ('xxxx', 'Cake', 0, 0);
insert into #sample VALUES ('Cake', 'xxxx', 2, 0);
insert into #sample VALUES ('Cake', 'xxxx', 2, 0);


select sum(case
           when Col1 = 'Cake' then Col3
           when Col2 = 'Cake' then Col4
           else 0
       end) as [value]
from  #sample

Answer 3

In MySQL, you can just do

SELECT SUM(IF(Col1 LIKE 'cake', Col3, Col4)) AS MySum

I'm not sure about the syntax of other variants. Incidentally, the already supplied answer of

SELECT
   SUM( 
     CASE 
         WHEN Col1 = 'Cake' THEN Col3 
         WHEN Col2 = 'Cake' THEN Col4 
         ELSE 0 END 
   ) AS MySum

doesn't give the correct results - according to the specification, Col4 should be added when 'cake' doesn't appear in Col1, not only if it appears in Col2.

Answer 4

So based on Franks and Duncan's answers the following should be all you need...

select SUM( Case 
                when Col1 = 'Cake' then Col3 
                else Col4 END ) as MySum
from TheView

Answer 5

In Oracle Craig's answer will work or you could use a decode like the following:

SELECT SUM(DECODE(Col1,'Cake',Col3,Col4)) FROM TheView;