query help SUM

Question

query help SUM

ayh1

Mr or Mrs. 500

Points: 581
More actions
January 28, 2015 at 2:28 am

#294276

Hi
I have a query which works:
SELECT dbo.tblGP_Practices.OrganisationCode, SUM(dbo.tblAppointments.AttendanceStatus) AS [Total Attended], dbo.tblAppointments.AttendanceStatus
FROM dbo.tblGP_Practices INNER JOIN
dbo.tblGP_PatientLink ON dbo.tblGP_Practices.GPPracticeID = dbo.tblGP_PatientLink.GPPracticeID INNER JOIN
dbo.tblPatient ON dbo.tblGP_PatientLink.PatientID = dbo.tblPatient.PatientID INNER JOIN
dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID
GROUP BY dbo.tblGP_Practices.OrganisationCode, dbo.tblAppointments.AttendanceStatus
HAVING (dbo.tblAppointments.AttendanceStatus = 1)
I want to add another column to count the SUM HAVING (dbo.tblAppointments.AttendanceStatus = 2) and SUM(dbo.tblAppointments.AttendanceStatus) AS [Did not attend]
How would I write this statement if possible
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ayh1 (1/28/2015)
Hi
I have a query which works:
SELECT dbo.tblGP_Practices.OrganisationCode, SUM(dbo.tblAppointments.AttendanceStatus) AS [Total Attended], dbo.tblAppointments.AttendanceStatus
FROM dbo.tblGP_Practices INNER JOIN
dbo.tblGP_PatientLink ON dbo.tblGP_Practices.GPPracticeID = dbo.tblGP_PatientLink.GPPracticeID INNER JOIN
dbo.tblPatient ON dbo.tblGP_PatientLink.PatientID = dbo.tblPatient.PatientID INNER JOIN
dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID
GROUP BY dbo.tblGP_Practices.OrganisationCode, dbo.tblAppointments.AttendanceStatus
HAVING (dbo.tblAppointments.AttendanceStatus = 1)
I want to add another column to count the SUM HAVING (dbo.tblAppointments.AttendanceStatus = 2) and SUM(dbo.tblAppointments.AttendanceStatus) AS [Did not attend]
How would I write this statement if possible
thanks

SELECT

gp.OrganisationCode,

SUM(CASE WHEN a.AttendanceStatus = 1 THEN 1 ELSE 0 END) AS [Total Attended],

SUM(CASE WHEN a.AttendanceStatus = 2 THEN 1 ELSE 0 END) AS [Did not attend]

FROM dbo.tblGP_Practices gp

INNER JOIN dbo.tblGP_PatientLink pl

ON gp.GPPracticeID = pl.GPPracticeID

INNER JOIN dbo.tblPatient p

ON pl.PatientID = p.PatientID

INNER JOIN dbo.tblAppointments a

ON p.PatientID = a.PatientID

WHERE a.AttendanceStatus IN (1,2)

GROUP BY gp.OrganisationCode

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 2

thanks seems to work if i wanted to add attendance status = 2,3,4,5,6,7 to the second line

SUM(CASE WHEN a.AttendanceStatus = 2 THEN 1 ELSE 0 END) AS [Did not attend]

how would I write this

thanks once again

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ayh1 (1/28/2015)
thanks seems to work if i wanted to add attendance status = 2,3,4,5,6,7 to the second line
SUM(CASE WHEN a.AttendanceStatus = 2 THEN 1 ELSE 0 END) AS [Did not attend]
how would I write this
thanks once again

Not sure what you mean, unless attendance status = 2,3,4,5,6,7 all correspond to [Did not attend] - in which case the adjustment is very simple. Post up what you think it might be 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 4

ayh1

Mr or Mrs. 500

Points: 581

January 28, 2015 at 4:20 am

#1773626

yep did not attend can correspond to 2,3,4,5,6,7

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

ayh1 (1/28/2015)
yep did not attend can correspond to 2,3,4,5,6,7

Good...so what do you think the query should look like?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 6

I tried altering the third line to:

SUM(CASE WHEN a.AttendanceStatus = 2,3,4,5,6,7 THEN 1 ELSE 0 END) AS [Did not attend]

and I tried

replacing the commas with OR

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

ayh1 (1/28/2015)
I tried altering the third line to:
SUM(CASE WHEN a.AttendanceStatus = 2,3,4,5,6,7 THEN 1 ELSE 0 END) AS [Did not attend]
and I tried
replacing the commas with OR

SUM(CASE WHEN a.AttendanceStatus IN (2,3,4,5,6,7) THEN 1 ELSE 0 END) AS [Did not attend]

Is this homework we're helping you with?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 8

Nope at work doing a side project in vba and sql which I have little knowledge in

ayh1 Mr or Mrs. 500 Points: 581 More actions · Answer 9

ayh1

Mr or Mrs. 500

Points: 581

January 28, 2015 at 5:05 am

#1773643

thanks for your help 🙂