How To Solve The Issue With Group By And Aggregate Function In Postgresql

I am trying to write a query for dividing the two SQL statements but it shows me ERROR: column 'temp.missed' must appear in the GROUP BY clause or be used in an aggregate funct

Solution 1:

You don't need all those subqueries - a count function applied to a case expression would be much simpler:

SELECT   doctor_id, 
         COUNT(CASE appointment_status 
                    WHEN'missed'THEN1ELSENULLEND)::float/COUNT(*) AS ratio
FROM     appointments
GROUPBY doctor_id

Solution 2:

You can simplify the query a lot and if you are using PostgreSQL 9.4 you can use a very nice and clear syntax:

SELECT   doctor_id, 
     COUNT(appointment_status)::floatFILTER (WHERE appointment_status='missed') /COUNT(*) AS ratio
FROM     appointments
GROUPBY doctor_id

The FILTER will affect only the COUNT it is after, so in this case it will only count missed appointments.