Solved

Help with MYSQL Query

Posted on 2004-10-24
350 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
Question by:kinko899
    8 Comments
     
    LVL 4

    Expert Comment

    by:pankajtiwary
    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
    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
    I have been doing some research on trying to figure out this query.  Would using the LIMIT statement help ?
    0
     

    Author Comment

    by:kinko899
    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
    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
    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:
    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
    petoskey-001...thanks for the reply and help.  I finally got it to work correctly :-)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    A lot has been said about MySQL server memory usage and methods for calculating or estimating the same. While going through the MySQL manual (http://dev.mysql.com/doc/refman/5.0/en/memory-use.html) and the MySQL Server Memory Usage (http://www.my…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    857 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now