Solved

MS Query Case When syntax error

Posted on 2015-01-24
4
204 Views
Last Modified: 2015-01-24
Can someone correct the syntax of the following MS Query SQL?  There is an error in the case when statement to sum the SIP field.  The SIP field is a Y/N field.

SELECT

tbl_NHSN_SSI_Pts.SurgeonID,
tbl_NHSN_SSI_Pts.Surgeon,  
Count(tbl_NHSN_SSI_Pts.SurgeonID) AS 'SURG',

sum(CASE WHEN tbl_NHSN_SSI_Pts.SIP = 'Yes' then 1 else 0 end ) as 'INF'

FROM `P:\IC-Employee Health\SSI\NHSN.accdb`.tbl_NHSN_SSI_Pts tbl_NHSN_SSI_Pts



GROUP BY tbl_NHSN_SSI_Pts.Surgeon, tbl_NHSN_SSI_Pts.SurgeonID
ORDER BY tbl_NHSN_SSI_Pts.Surgeon, tbl_NHSN_SSI_Pts.SurgeonID


Thanks

Glen
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40568424
By Yes/No field do you mean a bit field or a character field that stores "Yes" and "No". If the latter then your case statement should work.
0
 

Author Comment

by:GPSPOW
ID: 40568429
Yes.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40568432
For a bit datatype field, use this version of the CASE syntax

sum(CASE tbl_NHSN_SSI_Pts.SIP when 1 then 1 else 0 end ) as 'INF'
0
 

Author Closing Comment

by:GPSPOW
ID: 40568447
Thanks

I also found that if I change it to a MS-Access  IIF clause this works too.

Glen
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question