CODEWITHSUNDEEP
sqloracle-database
Media Player
Media Player

Reputation: 21

Need help on SQL query to find tuple(s) with max number

I have 2 tables for appointments and employees

APPT 

PatientSSN    ENum  Date
111111111     101   20180215
111111111     102   20181010
111111111     105   20190505
111111111     105   20190506
222222222     103   20190115
222222222     105   20190415
222222222     104   20181212
222222222     104   20181213
333333333     102   20190808
333333333     102   20190910
333333333     102   20191001
333333333     102   20191002
333333333     102   20191003
444444444     101   20190404
444444444     102   20190505
444444444     103   20190606
444444444     104   20190707
555555555     106   20190202
555555555     105   20190303
555555555     106   20190404
555555555     105   20190505
555555555     106   20190606
666666666     104   20190808
777777777     102   20180707
777777777     102   20190929
888888888     103   20190909
888888888     103   20190910
999999999     102   20190818
010101010     103   20190717
010101010     106   20190817
010101010     103   20190917
010101010     106   20191002
121212121     103   20190909
232323232     101   20181111
232323232     105   20190323
232323232     101   20190623
343434343     106   20190930
454545454     103   20190110
454545454     103   20190210
454545454     103   20190310
565656565     102   20190626
676767676     101   20190102
787878787     101   20190203
787878787     101   20190304
898989898     104   20190405
898989898     104   20190506
898989898     104   20190607

and EMP 

ENum  EName       Title
101   Zhivago     Doctor
102   Welby       Doctor
103   Jekyl       Doctor
104   Caligari    Doctor
105   Nightingale Nurse
106   Ratchet     Nurse

I need to find the DOCTOR(s) (employee number, name) who have the most appointments and the number of appointments.

I wrote this query:

SELECT appt.enum, COUNT(appt.enum) 
FROM appt
GROUP BY appt.enum 
HAVING COUNT (appt.enum)=(SELECT MAX (appointments)
                          FROM (SELECT appt.enum, COUNT(*) appointments
                                 FROM appt
                                 GROUP BY appt.enum));

Which gives me:

ENUM       COUNT(APPT.ENUM)
102        11

which returns the enum who has the max number of appointments (might not be a doctor, and might be a nurse), and the number of appointments

and this query:

SELECT enum, ename
FROM emp
WHERE ( emp.enum IN ( SELECT enum 
FROM emp
WHERE ( emp.title = 'Doctor' )));

which returns the enum and name of the doctors.

Question is how do I combine them to give the enum, name, and the number of appointments of doctor(s) with max number of visits?

Upvotes: 1

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you just want a join. In Oracle 12C, you can do:

SELECT e.enum, e.ename, COUNT(*) 
FROM appt a JOIN
     emp e
     ON a.enum = e.enum
GROUP BY e.enum, e.ename
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

In earlier versions, use window functions:

SELECT *
FROM (SELECT e.enum, e.ename, COUNT(*) as cnt,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM appt a JOIN
           emp e
           ON a.enum = e.enum
      GROUP BY e.enum, e.ename 
     ) e
WHERE seqnum = 1;

Upvotes: 1

Related Questions