Help with MYSQL Query

Hi...I am trying to write a query in MYSQL that will review my ATTENDS_EXAM table and determine which doctor has examined the most patients this week and display how many patients he or she examined.

I created the following query and it displays the results but it does it for all of the doctors and not just the one with the maximum exams.  Below is my query:

SELECT Doctor_Name, Count(Doctor_Name)
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017
GROUP BY Doctor_Name;

This is my output from my query:

Doctor_Name Count(Doctor_Name)
Dr. Smith                2
Dr. Jones                3

I am confused on how to go about modifying this query so it will only print out the results for Dr. Jones as he performed the most exams this week ?

I created the following two queries but none of them seem to work:

SELECT Doctor_Name
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017
GROUP_BY Doctor_Name
HAVING Max(Count(Doctor_Name));

SELECT Doctor_Name
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017 AND count(Doctor_Name) = Max(Count(Doctor_Name))
GROUP_BY Doctor_Name

SELECT Doctor_Name
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017 AND count(Doctor_Name) is Max(Count(Doctor_Name))
GROUP_BY Doctor_Name

None of these 3 queries seemed to work and I got error messages.  Any have some suggestions ?

Thanks :-)
kinko899Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
petoskey-001Connect With a Mentor Commented:
Sorry, typo.  I forgot the BY in Group By.  Also I didn't put in Desc to order by highest count first.  Here is the corrected SQL.


SELECT Doctor_Name, Count(Doctor_Name) as Number
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017
GROUP BY Doctor_Name
ORDER BY Number Desc
LIMIT 1;
0
 
pankajtiwaryCommented:
Hi kinko899,

Try this one.

doctor_name is the name of the doctor and no_of_patients is the number of patients.

select doctor_name, no_of_patients from attends_exam where examination_date > 20041017 and no_of_patients=max(no_of patients) ;

Cheers!
0
 
kinko899Author Commented:
Hi Pankajtiwary :-)

   Thanks for the reply.  Unfortunately your query will not work as it does not match my table structure.  This is the statement I used to create my table with all the data elements:  The combination of Hospital Code and Sequence number idenitifies a patient.  I hope this clarifies the question.

CREATE TABLE ATTENDS_EXAM
( Doctor_Name varchar(30) not NULL,
  Hospital_Code char(2) not NULL,
  Sequence_Number int not NULL,
  Time_of_Exam time not NULL,
  Examination_Date date not NULL,
  PRIMARY KEY (Doctor_Name, Hospital_Code, Sequence_Number, Time_of_Exam, Examination_Date)
);
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kinko899Author Commented:
I have been doing some research on trying to figure out this query.  Would using the LIMIT statement help ?
0
 
kinko899Author Commented:
I have been doing some more research and came up with the following query:

SELECT Doctor_Name, Count(Doctor_Name)
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017
GROUP BY Doctor_Name Desc
LIMIT 1;

It gives my the correct result but is this the correct use of DESCENDING and LIMIT ?
0
 
petoskey-001Commented:
The problem is not your grouping, but your ordering / sorting.  If your answer gives you the correct result, consider it luck at this point.  By default MySql will sort group by clauses by the field that is being grouped -- ie. Doctor_Name, that's why Jones comes before Smith.

This should work for you...

SELECT Doctor_Name, Count(Doctor_Name) as Number
FROM ATTENDS_EXAM
WHERE Examination_Date > 20041017
GROUP Doctor_Name
ORDER BY Number
LIMIT 1;

Remove Limit 1 to see that it's sorting correctly for you.
0
 
kinko899Author Commented:
petoskey-001

    Thanks for the reply.  I tried your query but it is not working for some reason.  I am using a MYSQL testbed and I am getting the following error when I tried your query:

Error 1064: You have an error in your SQL syntax near 'Doctor_Name ORDER BY Number LIMIT 1' at line 4
0
 
kinko899Author Commented:
petoskey-001...thanks for the reply and help.  I finally got it to work correctly :-)
0
All Courses

From novice to tech pro — start learning today.