Not getting correct COUNT() in MySQL Query (Modified Earlier )

Question

I have 3 tables named

1. com_event_schedules
2. com_appointments
3. com_event_schedules_com_appointment_c

which has a relation between first two tables.

Following are the fields of the tables

1. com_event_schedules -- id -- name -- schedule_date -- start_time -- end_time -- deleted
2. com_appointments -- id -- start_time -- end_time -- status
3. com_event_schedules_com_appointment_c -- id -- com_event_schedules_com_appointmentcom_event_schedules_ida (schedule_id) -- com_event_schedules_com_appointmentcom_appointment_idb (appointment_id)

relation between tables com_event_schedule and com_appointments is 1 to Many

What I want in result schedule_id, and total counts of its appointments who's status='completed'

I tried following query:

SELECT sch.id,COUNT(app.status) 
FROM 
com_event_schedules sch, 
com_appointment app, 
com_event_schedules_com_appointment_c rel 
WHERE 
sch.id=com_event_schedules_com_appointmentcom_event_schedules_ida AND 
app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND
app.status='completed'

What I'm expecting in result is id of each schedule with its COUNT of appointments which are having status "completed" and COUNT should be "0" if there is no match.

But What Im getting in result is Only schedule id who is having appointment with status completed and COUNT 1.

e.g

I'm having 2 schedule and for each I'm having 3 appointments. Out of which 1st schedule's one appointment is having status='completed'

So I need result like this

sch_id     app.status
1              1
2              0

Answer 1

Try this:

SELECT 
    sch.id,
    SUM(IF(app.status = 'completed' , 1, 0)) 
FROM 
    com_event_schedules sch
INNER JOIN  com_event_schedules_com_appointment_c rel 
    ON rel.com_event_schedules_com_appointmentcom_event_schedules_ida = sch.id 
INNER JOIN com_appointment app
    ON rel.com_event_schedules_com_appointmentcom_appointment_idb = app.id 
GROUP BY
    sch.id
HAVING
    COUNT(*) > 1

Answer 2

1) Although in certain circumstances you can achieve the same results, you want to use JOINS instead of selecting FROM multiple tables indiscriminately. What type of JOIN you want to use depends on the data you have, and what you want to do with it. In the example below I've just used LEFT JOINS.

For more, see: SQL left join vs multiple tables on FROM line?

2) Secondly, by including "Where app.status='completed'" you are excluding the possibility for retrieving information about appointments which are not completed (only returning positive counts). This is why we use a LEFT JOIN here, even where there are no matches for the join condition "status=completed" we will get a NULL on the right side.

For a nice visual explanation, see: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

3) Count is an aggregate function, so will only return 1 result - unless you group by a column to return results PER that column. In this case, you want to return a count of completed appointments per schedule, so you group by id (sch.id).

SELECT 
  sch.id,COUNT(app.status)
FROM
  com_event_schedules sch
LEFT JOIN 
  com_event_schedules_com_appointment_c  rel 
  ON 
  sch.id=rel.com_event_schedules_com_appointmentcom_event_schedules_ida
LEFT JOIN
  com_appointments app 
  ON 
  app.id=rel.com_event_schedules_com_appointmentcom_event_schedules_idb=app.id
  AND 
  app.status='completed'
GROUP BY 
  id

If you're not so familliar with joining or aggregation, and want to see how it works, then I would remove the GROUP BY and COUNT parts of the above query and simply look at non aggregated result set. It should make things a little clearer.

SELECT 
  sch.id,app.status
FROM
  com_event_schedules sch
LEFT JOIN 
  com_event_schedules_com_appointment_c  rel 
  ON 
  sch.id=rel.com_event_schedules_com_appointmentcom_event_schedules_ida
LEFT JOIN
  com_appointments app 
  ON 
  app.id=rel.com_event_schedules_com_appointmentcom_event_schedules_idb=app.id
  AND 
  app.status='completed'