StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Query two different conditions for different count in 1 query
text
Body
copied!<p>I have 3 tables named </p> <ol> <li>com_event_schedules</li> <li>com_appointments</li> <li>com_event_schedules_com_appointment_c </li> </ol> <p>which has a relation between first two tables. </p> <p>Following are the fields of the tables</p> <ol> <li><p>com_event_schedules -- id -- name -- schedule_date -- start_time -- end_time -- deleted</p></li> <li><p>com_appointments -- id -- start_time -- end_time -- status</p></li> <li><p>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)</p></li> </ol> <p>relation between tables com_event_schedule and com_appointments is 1 to Many</p> <p>What I want result having schedule_id, and total counts of its appointments on condition status='completed'</p> <p>I tried following query:</p> <pre><code>SELECT sch.id,COUNT(app.id) AS total, (SELECT COUNT(ap.id) FROM com_appointment ap, com_event_schedules sc, com_event_schedules_com_appointment_c re WHERE re.com_event_schedules_com_appointmentcom_event_schedules_ida=sc.id AND ap.id=re.com_event_schedules_com_appointmentcom_appointment_idb AND sc.deleted=0 AND ap.status='completed') AS completed FROM com_event_schedules sch, com_appointment app, com_event_schedules_com_appointment_c rel WHERE rel.com_event_schedules_com_appointmentcom_event_schedules_ida=sch.id AND app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND sch.deleted=0 GROUP BY sch.id </code></pre> <p>Using this query Im getting accurate total count but completed count is not as expected. it is showing 1 for each schedule. However only 1 appointment in db is completed and others are still pending.</p> <p>Is there something wrong with query ?? I have SugarCRM in backend. Cant use fiddle cause relation and fields are too messy.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload