SUM function with 'bit' data type 1

[UHNSTrust]

I am trying to convert an access query to a stored procedure but am getting an error

ADO error: The sum or average aggregate operation cannot take a bit data type as an argument.

The SQL I am getting the error on is

SELECT tblPatient.PatientHospitalNo, tblReferral.ReferralID, tblReferral.ReferralIsFastTrackReferral, tblReferral.ReferralCaseCompleted, Sum(tblReferralAppointment.ReferralAppointmentAccepted) AS SumOfReferralAppointmentAccepted
FROM (tblReferral INNER JOIN tblPatient ON tblReferral.ReferralPatientID = tblPatient.PatientID) INNER JOIN tblReferralAppointment ON tblReferral.ReferralID = tblReferralAppointment.ReferralAppointmentReferralID
GROUP BY tblPatient.PatientHospitalNo, tblReferral.ReferralID, tblReferral.ReferralIsFastTrackReferral, tblReferral.ReferralCaseCompleted, tblReferral.ReferralDateOfReferral
HAVING (((tblReferral.ReferralIsFastTrackReferral)=1) AND ((tblReferral.ReferralCaseCompleted) Is Null) AND ((Sum(tblReferralAppointment.ReferralAppointmentAccepted))<>1) AND ((tblReferral.ReferralDateOfReferral)>DATEADD(day, - 45, GETDATE())))

What I am trying to achieve is to look at appointments and check that for each 'PatientHospitalNo' that they have accepted one appointment (the 'ReferralAppointmentAccepted' field is a tick box - bit data type). If the sum of field 'ReferralAppointmentAccepted' <>1 then I need to show the 'PatientHospitalNo' as they have either not accepted an appointment or have accepted more than 1 appointment.

Can somebody help with how I can 'sum' a bit data type field.

Thanks in advance

Jonathan

 

[jby1]

I think if you use COUNT on the bit field, and then in the HAVING check if the value of the bit is 1, you will get the result you are after.

 

[vongrunt]

Not sure... HAVING applies after GROUP BY.

Cast/convert tblReferralAppointment.ReferralAppointmentAccepted (** sore fingers **) as integer, that's the safest shot.

 

[UHNSTrust]

Thanks for the reply jby1.

Trying to get my head around what your saying.

If I use count on the bit field if its value is 1 then it will let me know if there is 1 or more appointments accepted but will not count if there are no appointments accepted because those appointments are not in the recordset.

The reason for using 'SUM' was because if the sum value was 0 I knew no appointments had been accepted or if it was above 1 I knew there was more than 1 appointment accepted. With COUNT you can only know the amount of appointments.

This is giving me a headache!!!

Jonathan

 

[UHNSTrust]

Vongrunt, thanks for the reply. Cast to an integer was what I needed.

Thanks again

Jonathan

 

[jby1]

sorry for my reply, wasn't thinking straight.

i know there is a way to do it using count, but can't remember exactly how

 

[cmmrfrds]

On the count, 1 and 0 would be counted, but Nulls would not. So, the nullif function should do it.

I don't remember the exact syntax, but something like.

count(nullif(field,0))

 

[ESquared]

Sum(CASE WHEN BitField = 1 THEN 1 ELSE 0 END)

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)