I have a table like this
The first table is the "Appointments" table with 'doctor_id' the second table is the "Doctors" table also with 'doctor_id'. The duplicate values from the appointments table represents how many appointment each doctor has. for example the doctor_id(50) has 6 appointments.
doctor_id(Appointments) | doctor_id(Doctors)
50 | 50
50 | 51
50 | 52
52 | 53
50 |
50 |
52 |
53 |
50 |
Now, my question is how would I show this table below (ps excluding the doctor id '51)
doctor_id (Doctors) | Count
50 | 6
52 | 2
53 | 1
This what i have tried so far, i have managed to work out how many doctors have appointments using :
SELECT COUNT(*) FROM appointment
WHERE doctor_id > 50
I have executed other queries as well but with no luck.
Assuming doctor_id is unique in the doctors table...
SELECT d.doctor_id AS doctor_id
, COUNT(a.doctor_id) AS appointment_count
FROM doctors d
LEFT
JOIN appointments a
ON a.doctor_id = d.doctor_id
GROUP BY d.doctor_id
To get the count by doctor_id, you need to GROUP BY the doctor_id. You don't have to include doctor_id in the SELECT list, but if you only return the COUNT(), you'd just get a list of counts:
appointment_count
-----------------
6
0
2
1
With no indication of which appointment_count goes with which doctor_id. So, the usual pattern is to include what you GROUP BY in the SELECT list.
In order to return the 0 count for doctor 51, we want an outer join to the appointments table, so the row from the doctors table is returned even when there is no match.
If you only want rows for doctors that have one or more appointments, then you can just query the appointments table. (This query won't return any "zero" count for a doctor.)
SELECT a.doctor_id
, COUNT(1) AS appointment_count
JOIN appointments a
GROUP BY a.doctor_id
Again, the GROUP BY clause is necessary to get a count of rows for each distinct value of doctor_id. That effectively collapses the rows for each doctor_id into a single row. The aggregate function operates on the "group" of rows.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments