SQL Challenges-1: Count the number of patients treated by each doctor

SQL Challenges-1: Exercise-64 with Solution

From the following tables write a query in SQL to count the number of patients treated by each doctor. Return doctors name and number of patients they treated.

Table: doctors

Structure:

Data:

Table: visits

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table doctors(
doctor_id integer not null unique,
doctor_name		varchar(25),
degree			varchar(10));

insert into doctors values(210,'Dr. John Linga','MD');
insert into doctors values(211,'Dr. Peter Hall','MBBS');
insert into doctors values(212,'Dr. Ke Gee    ','MD');
insert into doctors values(213,'Dr. Pat Fay   ','MD');



create table visits(
doctor_id integer ,
patient_name		varchar(25),
vdate			date,
foreign key(doctor_id) references doctors(doctor_id));

insert into visits values(210,'Julia Nayer ','2013-10-15');
insert into visits values(213,'TJ Olson    ','2013-10-14');
insert into visits values(211,'John Seo    ','2013-10-15');
insert into visits values(212,'James Marlow','2013-10-16');
insert into visits values(212,'Jason Mallin','2013-10-12');
insert into visits values(213,'Dean Hops    ','2013-10-18');
insert into visits values(212,'Peter Kent','2013-10-19');
insert into visits values(212,'Moody Hogs','2013-10-25');


select doctor_name,count(*) 'Patients Treated' 
from visits v
join doctors d
on v.doctor_id=d.doctor_id
group by 1;

Sample Output:

doctor_name   |Patients Treated|
--------------+----------------+
Dr. John Linga|               1|
Dr. Peter Hall|               1|
Dr. Ke Gee    |               4|
Dr. Pat Fay   |               2|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find the highest daily total order for an item.
Next: Find total order amount for each customer in September 2008.