Currently I have three tables, namely: hospital, doctor and test. The report generation time is calculated using report time minus the sample test time. How can I write a query to find out which hospital has the least average report generation time?
Table name: hospital
|name|
| 'A' | | 'B' | | 'C' |
Table name: doctor
| id | doctor_name | hospital_name |
|---|---|---|
| 1 | 'Eric' | 'A' |
| 2 | 'Peter' | 'A' |
| 3 | 'Sam' | 'B' |
| 4 | 'Park' | 'B' |
| 5 | 'Alan' | 'C' |
| 6 | 'Andrew' | 'C' |
Table name: test
| id | test_date | test_time | report_date | report_time | doctor_id |
|---|---|---|---|---|---|
| 1 | '2021-10-9' | '19:30:00' | '2021-10-9' | '23:30:00' | 1 |
| 2 | '2021-10-9' | '14:00:00' | '2021-10-9' | '22:20:00' | 2 |
| 3 | '2021-10-9' | '20:30:00' | '2021-10-10' | '08:00:00' | 3 |
| 4 | '2021-10-10' | '07:30:00' | '2021-10-10' | '13:10:00' | 4 |
| 5 | '2021-10-10' | '09:30:00' | '2021-10-10' | '13:30:00' | 5 |
| 6 | '2021-10-10' | '11:00:00' | '2021-10-10' | '22:00:00' | 6 |
| 7 | '2021-10-10' | '15:20:00' | '2021-10-10' | '19:00:00' | 1 |
| 8 | '2021-10-11' | '08:00:00' | '2021-10-11' | '20:00:00' | 4 |
the column hospital_name in table 'doctor' references hospital.name and the column doctor_id in table 'test' references doctor.id
Expected result:
hospital_name 'A'
Explanation: From the 'test' table, we can get the average report generation time of Hospital 'A' is 320 minutes since there are three records about Hospital 'A' (the doctors whose id are 1 and 2) and the average report generation time of Hospital 'B' is 1630 / 3 minutes since there are three records about Hospital 'B' (the doctors whose id are 3 and 4) and the average report generation time of Hospital 'C' is 450 minutes since there are two records about Hospital 'C' (the doctors whose id are 5 and 6). As a result, the answer is Hospital 'A'.
select TIMESTAMPDIFF(minute, timestamp(test_date,test_time),
timestamp(report_date, report_time)) from test as t
inner join doctor as d
on t.doctor_id = d.id
inner join hospital as h
on d.hospital_name = h.name;
I don't know how to find the average generation time
CodePudding user response：
select h.name as hospital_name, count(t.id) as total_tests, SUM(TIMESTAMPDIFF(minute, timestamp(test_date,test_time),
timestamp(report_date, report_time))) as total_time, (SUM(TIMESTAMPDIFF(minute, timestamp(test_date,test_time),
timestamp(report_date, report_time)))/count(t.id)) as avg_time from test as t
inner join doctor as d
on t.doctor_id = d.id
inner join hospital as h
on d.hospital_name = h.name group by h.id
CodePudding user response：
After executing query, you can get these results, check them