problem with case when - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with case when

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 01:19:05

Dear Experts,

Select Module, Sum ((Case[Type] when 'FBA' then 1 else 0 END)) as SumFBA,
Sum((Case when [Type] <> 'FBA' then 1 else 0 END)) as SumNotFBA,
Sum(studentId)
from WarningLetter
group by Module

When I execute the above I got an error saying "Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for sum operator."

How do I select the module, the sum of all module whose type is "FBA", the sum of all module whose type is not "FBA", and the total number of records for each module?

Thank you in advance for your kind assistance.

regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-15 : 01:48:21

What is the datatype of studentId?

Madhivanan

Failing to plan is Planning to fail

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 02:37:54

varchar(50).

check out my blog at http://www.aquariumlore.blogspot.com

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 02:39:32

oops. I got the hint, I should not have used sum, but used count instead.. Thank you . :)

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:09:53

quote:
Originally posted by EugeneLim11

oops. I got the hint, I should not have used sum, but used count instead.. Thank you . :)

check out my blog at http://www.aquariumlore.blogspot.com

but why is StudentId declared varchar? Id should be int right?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-15 : 07:39:06

quote:
Originally posted by visakh16

quote:
Originally posted by EugeneLim11

oops. I got the hint, I should not have used sum, but used count instead.. Thank you . :)

check out my blog at http://www.aquariumlore.blogspot.com

but why is StudentId declared varchar? Id should be int right?

Not neccessarily a general rule. It depends on the Business rule

Madhivanan

Failing to plan is Planning to fail

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 23:10:54

quote:
--------------------------------------------------------------------------------
Originally posted by EugeneLim11

oops. I got the hint, I should not have used sum, but used count instead.. Thank you . :)

check out my blog at http://www.aquariumlore.blogspot.com

----------------------------------------------------------------------

but why is StudentId declared varchar? Id should be int right?

--------------------------------------------------------------

yes, it is because of business rules. The studentID is alphanumberic.
i.e. ProgramName + studentNumber. I should have use the count to count total number of students.

What I am trying to do is to select the module, the sum of all module whose type is "FBA", the sum of all module whose type is not "FBA", and the total number of records for each module..

E.g.
Modules SumFBA SumNotFBA count(studentId)
Maths A 20 20 40
Statistics 14 15 29

;)

Sum(studentId) is not appropriate for this, and the count() function is more appropriate.

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:19:46

yeah...got that...cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources