Case Statement Problem

[partridge80]

In the following problem the variables 'BoxtypeA - H are varchars. A '1' can appear in any of them. bx1 - 8 are the corresponding money fields of BoxtypeA - H. I want Box1 to be the sum of bx's where there is a '1' in the corresponding Boxtype A- H. So what is wrong with this? (From a very novice user)

‘Box1’= Case
When BoxtypeA = ‘1’ Then bx1
When BoxtypeB =‘1’ Then ‘Box1’ + bx2
When BoxtypeC =‘1’ Then ‘Box1’ + bx3
When BoxtypeD =‘1’ Then ‘Box1’ + bx4
When BoxtypeE =‘1’ Then ‘Box1’ + bx5
When BoxtypeF =‘1’ Then ‘Box1’ + bx6
When BoxtypeG =‘1’ Then ‘Box1’ + bx7
When BoxtypeH =‘1’ Then ‘Box1’ + bx8
END

 

[MeanGreen]

Try this:
select
'Box1’= SUM(Case
When BoxtypeA = ‘1’ Then bx1
When BoxtypeB =‘1’ Then bx2
When BoxtypeC =‘1’ Then bx3
When BoxtypeD =‘1’ Then bx4
When BoxtypeE =‘1’ Then bx5
When BoxtypeF =‘1’ Then bx6
When BoxtypeG =‘1’ Then bx7
When BoxtypeH =‘1’ Then bx8
ELSE
0
END)
from MYTABLE

Hope this helps.

 

[partridge80]

Thanks MeanGreen,

I tried the above and received an error message:

Error 8118: Column 'bx1' is invalid in the select list because it is not contained in an aggregate function and there is no Group by clause. This error message was repeated for each variable. I then tried adding GROUP BY 'Box 1' at the end and received the following:

Error 164: Group By expressions must refer to column names that appear in the select list.

Any ideas?

 

[MeanGreen]

Try this:
select
'Box1’= SUM(Case
When BoxtypeA = ‘1’ Then bx1
When BoxtypeB =‘1’ Then bx2
When BoxtypeC =‘1’ Then bx3
When BoxtypeD =‘1’ Then bx4
When BoxtypeE =‘1’ Then bx5
When BoxtypeF =‘1’ Then bx6
When BoxtypeG =‘1’ Then bx7
When BoxtypeH =‘1’ Then bx8
ELSE
0
END)
group by
Case
When BoxtypeA = ‘1’ Then bx1
When BoxtypeB =‘1’ Then bx2
When BoxtypeC =‘1’ Then bx3
When BoxtypeD =‘1’ Then bx4
When BoxtypeE =‘1’ Then bx5
When BoxtypeF =‘1’ Then bx6
When BoxtypeG =‘1’ Then bx7
When BoxtypeH =‘1’ Then bx8
ELSE
0
END

Hope this helps.

 

[tlbroadbent]

Do you want to summarize the rows in the table or get the sum of the columns in each row? It appears to me that you want the latter. In that case, use the following query structure. If I'm incorrect in my assumption, let me know.

Select
Box1=
Case When BoxtypeA ='1' Then bx1 Else 0 End +
Case When BoxtypeB ='1' Then bx2 Else 0 End +
Case When BoxtypeC ='1' Then bx3 Else 0 End +
Case When BoxtypeD ='1' Then bx4 Else 0 End +
Case When BoxtypeE ='1' Then bx5 Else 0 End +
Case When BoxtypeF ='1' Then bx6 Else 0 End +
Case When BoxtypeG ='1' Then bx7 Else 0 End +
Case When BoxtypeH ='1' Then bx8 Else 0 End
From YourTable Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.