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.
| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2011-03-16 : 12:03:53
|
| When I run the query below:select Sponsorid ,count(distinct sponsorid) as SponsoridCT ,sum(sponsorflag) as CoveredEmployees,case when sponsorflag = '0' then count(sponsorflag) end as CoveredDependents, AnnMMfrom temp66093awhere month(pdt) = '12' group by sponsorid, annmm I get the following error:Msg 8117, Level 16, State 1, Line 1Operand data type varchar is invalid for sum operator.How do I convert the varchar in the sum function to a number? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-16 : 12:20:17
|
| select Sponsorid ,count(distinct sponsorid) as SponsoridCT ,sum(case when sponsorflag <> '0' then 1 else 0 end) as CoveredEmployees,sum(case when sponsorflag = '0' then 1 else 0 end) as CoveredDependents, AnnMMfrom temp66093awhere month(pdt) = 12group by sponsorid, annmm JimEveryday I learn something that somebody else already knew |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-03-16 : 12:26:24
|
Great, Jim. Thanks so much for the help!quote:
Originally posted by jimf
select Sponsorid ,count(distinct sponsorid) as SponsoridCT ,sum(case when sponsorflag <> '0' then 1 else 0 end) as CoveredEmployees,sum(case when sponsorflag = '0' then 1 else 0 end) as CoveredDependents, AnnMMfrom temp66093awhere month(pdt) = 12group by sponsorid, annmm JimEveryday I learn something that somebody else already knew
|
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-16 : 12:45:32
|
| You're welcome!JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Conversion Question