Solved

MS Query Case When syntax error

Posted on 2015-01-24
4
123 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
  • 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 39
Show Results for Latest DateTime in a View 27 24
transaction in asp.net, sql server 6 31
sumifs excel 2013 3 12
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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