[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help with MYSQL Query

Posted on 2004-10-24
8
Medium Priority
?
352 Views
Last Modified: 2012-06-27
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 :-)
0
Comment
Question by:kinko899
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 4

Expert Comment

by:pankajtiwary
ID: 12396428
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
 

Author Comment

by:kinko899
ID: 12396477
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
 

Author Comment

by:kinko899
ID: 12396732
I have been doing some research on trying to figure out this query.  Would using the LIMIT statement help ?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:kinko899
ID: 12396811
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
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12400397
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
 

Author Comment

by:kinko899
ID: 12400547
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
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 2000 total points
ID: 12401084
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
 

Author Comment

by:kinko899
ID: 12402579
petoskey-001...thanks for the reply and help.  I finally got it to work correctly :-)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question