Counting Column with Specific Value

Question

I'm looking to calculate the percent of times that the patient ("Full_name") has been on-time to their appointment in the query below. In theory I could count out many times the value is "On-time" and how many times its "Late" and devise lates by on times and multiply it by 100 to get the percent but I can't figure out the syntex required to get that calculation to show in a column and then have the patients grouped by their percentage of on-time score.

Example query:

SELECT 

Db.Full_Name,
CASE WHEN (Db.DOCTOR_APPOINTMENT_DATE - Db.ARRIVE_DOCTOR_DATE) + (Db.DOCTOR_APPOINTMENT_TIME - Db.ARRIVE_DOCTOR_TIME) < 0 THEN 'LATE' ELSE 'ON-TIME' END AS STATUS,

FROM Db

WHERE Db.DOCTOR_APPOINTMENT_DATE between to_date('10/30/2018','MM/DD/YYYY') and to_date ('12/30/2018','mm/dd/yyyy')

Example Output as currently written:

Full_name:       Status:
John Smith       On-Time
John Smith       On-Time
John Smith       Late
John Brown      On-Time
John Brown      On-Time
James White     Late 
John Brown      On-Time
John Brown      Late
James White     Late 
James White     Late 
James White     On-Time

What I'd like it to do:

Full_Name:         % on time:
John Brown          75%
James White         25%
John Smith          66%

Thanks for your time!

Tag your question with the database you are using. – Gordon Linoff Jan 03 '19 at 21:47 — Gordon Linoff, Jan 03 '19 at 21:47

score 1 · Answer 1 · answered Jan 03 '19 at 21:48

You can use aggregation for this, in particular, avg():

SELECT Db.Full_Name,
       AVG(CASE WHEN (Db.DOCTOR_APPOINTMENT_DATE - Db.ARRIVE_DOCTOR_DATE) + (Db.DOCTOR_APPOINTMENT_TIME - Db.ARRIVE_DOCTOR_TIME) < 0
                THEN 1.0 ELSE 0.0
           END) as late_ratio
FROM Db
WHERE Db.DOCTOR_APPOINTMENT_DATE BETWEEN DATE '2018-10-30 AND DATE '2018-12-30'
GROUP BY db.Full_Name

score 0 · Accepted Answer · answered Jan 03 '19 at 21:58

A sum Case statement would work well here. You simply need to count and add up each time the doctor/patient is on time. Then you need to know the sum of all the appointments to get an accurate percentage. I would NOT use the average for the single rows.

SELECT query with CASE condition and SUM()

     SELECT 

    Db.Full_Name,

    SUM( 
       CASE WHEN (Db.DOCTOR_APPOINTMENT_DATE - Db.ARRIVE_DOCTOR_DATE) +
   (Db.DOCTOR_APPOINTMENT_TIME - Db.ARRIVE_DOCTOR_TIME) < 0 THEN 0 ELSE 1 END

) AS OnTimeCount


--*********************************************
-- insert logic to count the total appointments and divide.**

 , count(somefield) as AppointmentCount

 --******************************
 -- loose example ---

, ( OnTimeCount /  Cast(AppointmentCount as float)) * 100 as OnTimeRatio
FROM Db

WHERE Db.DOCTOR_APPOINTMENT_DATE between to_date('10/30/2018','MM/DD/YYYY') and to_date ('12/30/2018','mm/dd/yyyy')

I think this is the right direction I need, but I am running into an error with the group by clause I think. — Chris Hart, Jan 03 '19 at 22:54

Counting Column with Specific Value

2 Answers2