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 |
|
jimtimber
Yak Posting Veteran
60 Posts |
 Posted - 2014-09-01 : 08:25:52
|
| Hi guys,I'm converting a lot of slow Access reports onto a new SQL based system. Can anyone advise the best way to write the following Access sum query that has an 'iif' statement in it. I'm thinking it should be a CASE statement but I can't get it to work?Infec Dis: Sum(IIf([Action] Like 'NOTIFIABLE Outbreak',1,0))The above is counting the number of 'Action's that are like 'NOTIFIABLE Outbreak'. Do I need to convert this into numeric before hand or can I get around this on SQL please?Thanks,JimJim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-09-01 : 08:31:19
|
SUM(CASE WHEN [Action] = 'NOTIFIABLE Outbreak' THEN 1 ELSE 0 END)
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-09-01 : 08:34:29
|
| Thanks SwePeso, i'll give it a try and feedback soon :)JJim |
|
|
|
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-09-01 : 08:40:43
|
| That works, thank you once again :)JJim |
|
|
|
|
|
|
CASE statement with SUM...from Access to SQL