CODEWITHSUNDEEP
mysqlsql
Shibbir
Shibbir

Reputation: 2031

How to get total appointment durations from this table using sql

I have 3 mysql tables:

appointments

id  slot_id patient_name    doctor_id   deleted_at
1   11      Tasin           23          2019-10-10
2   12      Nawaz           22          null
3   13      Rakib           23          null
4   14      Hossen          23          null
5   15      Aritra          24          null
6   16      Anik            22          null
7   17      Manik           22          null

doctors

id  status  doctor_name
22  1       Khaled
23  1       Hasan
24  0       Rumi

slots

id  date        duration    time
11  2019-10-10  2900        01:01
12  2019-10-11  1200        02:01
13  2019-10-18  1100        03:01
14  2019-09-08  200         11:01
15  2019-08-01  500         01:31
16  2019-10-07  300         02:31
17  2019-10-02  1200        03:31

Now, I want to show a list of doctors with their total appointment durations in decreasing order using SQL query.

Unfortunately, I don't have any idea about this SQL query. Can you assist me?

Upvotes: 0

Views: 162

Answers (2)

Ladi Oyeleye
Ladi Oyeleye

Reputation: 154

SELECT DOCTOR_NAME, SUM(DURATION) FROM APPOINTMENTS A
JOIN DOCTORS D ON D.ID = A.DOCTOR_ID
JOIN SLOTS S ON S.ID = A.SLOT_ID
GROUP BY D.ID, DOCTOR_NAME
ORDER BY SUM(DURATION) DESC;

Upvotes: 1

Ankit Sharma
Ankit Sharma

Reputation: 69

select d.doctor_id, d.doctor_name, sum(apt.duration) as total_duration from 
doctors as d
join appointments as apt on apt.doctor_id = d.doctor_id
join slots as s on s.id = apt.slot_id
group by d.doctor_id, d.doctor_name

The above query should work fine. There might be some typo as I didn't write it in the SQL management studio.

Upvotes: 1

Related Questions