CODEWITHSUNDEEP
mysql
Nikita Gusev
Nikita Gusev

Reputation: 143

Count records for previous month SQL

Can you help me with a simple task, but I can't really figure out, how I should do that request. Help, please.

I have:

doctors
id name             profession 
 1 James Harden     dental 
 2 James Jones      therapist 
 3 LeBron James     cardiologist 
 4 Kobe Braynt      surgeon 
 5 Sabrina Williams nurse 
 6 Tyler Okonma     speech therapist 

patients 
id name         diagnostic 
 1 Mo Bamba     tooth pulling out
 2 Kaney West   astma 
 3 Post Malone  heart attack 
 4 Denzel Curry headache 
 5 Nicola Jokic stomac-ache 
 6 Dwayne Wade  AIDS 

visits
doctorId patientId visitDate 
       1         1 2019-03-09
       2         4 2019-03-01
       2         5 2019-02-26
       2         6 2019-02-05
       3         3 2019-03-03
       4         2 2019-03-07

I need to show the doctors serving the largest number of patients in the previous month. I am lost in counting the patients which were serving by doctors. Can you suggest the request, please?

Upvotes: 0

Views: 375

Answers (2)

forpas
forpas

Reputation: 164089

You need to group by doctorid the visits table and join to the doctors table:

select d.name, g.counter
from doctors d inner join (
  select doctorid, count(distinct patientid) counter
  from visits
  where 
    year(visitdate) = year(current_date - interval 1 month)
    and 
    month(visitdate) = month(current_date - interval 1 month)
  group by doctorid
  order by counter desc limit 3
) g on g.doctorid = d.id
order by g.counter desc, d.name

You can change limit 3 to what you like.

Upvotes: 1

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

Try this

select d.name, count( distinct v.patientid) noofvisits
from visits v inner join 
    doctors d on v.doctorid=d.doctorid
where v.visitdate < '1-mar-2019'
group by d.name
order by noofvisits desc

Upvotes: 1

Related Questions