CODEWITHSUNDEEP
sqlgroup-by
Stewage
Stewage

Reputation: 261

SQL - Displaying entries that are the max of a count?

CREATE TABLE doctor( patient CHAR(13), docname CHAR(30) );

Say I had a table like this, then how would I display the names of the doctors that have the most patients? Like if the most was three and two doctors had three patients then I would display both of their names.

This would get the max patients:

SELECT MAX(count) 
FROM (SELECT COUNT(docname) FROM doctor GROUP BY docname) a;

This is all the doctors and how many patients they have:

SELECT docname, COUNT(docname) FROM doctor GROUP BY name;

Now I can't figure out how to combine them to list only the names of doctors who have the max patients.

Thanks.

Upvotes: 26

Views: 106032

Answers (9)

Kevin Swann
Kevin Swann

Reputation: 1038

While using ... HAVING COUNT(*) = ( ...MAX().. ) works:

  • Within the query, it needs almost the same sub-query twice.
  • For most databases, it needs a 2nd level sub-query as MAX( COUNT(*) ) is not supported.

While using TOP / LIMIT / RANK etc works:

  • It uses SQL extensions for a specific database.

Also, using TOP / LIMIT of 1 will only give one row - what if there are two or more doctors with the same maximum number of patients?

I would break the problem into steps:

Get target field(s) and associated count

SELECT docName, COUNT( patient ) AS countX
FROM doctor
GROUP BY docName

Using the above as a CTE, (a statement scoped view), use this to get the max count row(s)

WITH x AS
(
    SELECT docName, COUNT( patient ) AS countX
    FROM doctor
    GROUP BY docName
)
SELECT x.docName, x.countX
FROM x
WHERE x.countX = ( SELECT MAX( countX ) FROM x )

The WITH clause, which defines a CTE, effectively gives named sub-queries that can be re-used within the same query.

While this solution, using CTEs, is longer, it is:

  • Easier to test
  • Self documenting
  • Extendable

It is easier to test as parts of the query can be run standalone.

It is self documenting as the query directly reflects the requirement ie the CTE lists the target field(s) and associated count.

It is extendable as if other conditions or fields are required, this can be easily added to the CTE. eg in this case, the table structure should be changed to include a doctor-id as a primary key field and this should be part of the results.

Upvotes: 4

lehanh
lehanh

Reputation: 585

if you do not need to care about performance I think just sorting and pick first element. Something like this:

SELECT docname, COUNT(docname) as CNT 
FROM doctor 
WHERE docname = docname 
GROUP BY docname
ORDER BY CNT DESC
LIMIT 1

Upvotes: 0

SASMIT VINCHURKAR
SASMIT VINCHURKAR

Reputation: 1

This will give you each doctor name and respective count of treating patients

SELECT docname, COUNT(docname) as TreatingPatients FROM doctor
WHERE docname = docname
GROUP BY docname

Upvotes: -1

Boney
Boney

Reputation: 2202

Another alternative using CTE:

with cte_DocPatients
as
(
select docname, count(*) as patientCount
from doctor
group by docname
)
select docname, patientCount from 
cte_DocPatients where
patientCount = (select max(patientCount) from cte_DocPatients)

Upvotes: 0

Manish Singh
Manish Singh

Reputation: 6188

This should do it.

SELECT docname, COUNT(*) FROM doctor GROUP BY name HAVING COUNT(*) = 
    (SELECT MAX(c) FROM
        (SELECT COUNT(patient) AS c
         FROM doctor
         GROUP BY docname))

On the other hand if you require only the first entry, then

SELECT docname, COUNT(docname) FROM doctor 
GROUP BY name 
ORDER BY COUNT(docname) DESC LIMIT 1;

Upvotes: 32

Thomas
Thomas

Reputation: 64674

Allowing for any feature in any ISO SQL specification since you did not specify a database product or version, and assuming that the table of patients is called "patients" and has a column called "docname", the following might give you what you wanted:

With PatientCounts As
    (
    Select docname
        , Count(*) As PatientCount
    From patient
    Group By docname
    )
    , RankedCounts As
    (
    Select docname, PatientCount
        , Rank() Over( Order By PatientCount ) As PatientCountRank
    From PatientCounts
    )
Select docname, PatientCount, PatientCountRank
From RankedCounts 
Where PatientCountRank = 1

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 839254

Here's another alternative that only has one subquery instead of two:

SELECT docname
FROM author
GROUP BY name
HAVING COUNT(*) = (
    SELECT COUNT(*) AS c
    FROM author
    GROUP BY name
    ORDER BY c DESC
    LIMIT 1
)

Upvotes: 6

squillman
squillman

Reputation: 13651

This should do it for you:

SELECT docname
FROM doctor
GROUP BY docname
HAVING COUNT(patient)=
    (SELECT MAX(patientcount) FROM
        (SELECT docname,COUNT(patient) AS patientcount
         FROM doctor
         GROUP BY docname) t1)

Upvotes: 12

JonH
JonH

Reputation: 33183

Take both queries and join them together to get the max: 

 SELECT
      docName,
      m.MaxCount
    FROM
      author
    INNER JOIN
     (
      SELECT 
            MAX(count)  as MaxCount,
            docName
      FROM 
            (SELECT 
                  COUNT(docname) 
             FROM 
                  doctor 
             GROUP BY 
                  docname
            )
      ) m ON m.DocName = author.DocName

Upvotes: 1

Related Questions