CODEWITHSUNDEEP
mysqlsqlsubquery
Ofek Ron
Ofek Ron

Reputation: 8578

SQL Nested Query Homework SQL Fiddle

Find Insurance Company with the maximal number of members (patients).

Thats the Database and my first attempt to get closer :

http://sqlfiddle.com/#!3/4b2ed/27

SELECT  P.cid AS cid, COUNT ( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid

how do i get the max countp?

Upvotes: 2

Views: 295

Answers (4)

eggyal
eggyal

Reputation: 125935

Just order your results in descending numbers of patients, and take only the first record.

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
ORDER BY    CountP DESC
LIMIT 1

If more than one insurance company might have that maximal number of members and you want to fetch all of them, you will need to use a subquery:

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
HAVING      CountP = (
    SELECT   COUNT(pid) AS c
    FROM     Patient
    GROUP BY cid
    ORDER BY c DESC
    LIMIT 1
);

In the strange situation you describe in your comments, where one is unable to use LIMIT, you could do:

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
HAVING      CountP = (SELECT MAX(d.c) FROM (
    SELECT   COUNT(pid) AS c
    FROM     Patient
    GROUP BY cid
) AS d);

Upvotes: 4

Benjamin Cox
Benjamin Cox

Reputation: 6120

Wow - lots of constraints. Here you go:

SELECT MAX(CountP) FROM (
  SELECT P.cid, COUNT ( P.pid ) AS CountP
  FROM  Patient P
  GROUP BY  P.cid
) winner

Upvotes: 0

SqlACID
SqlACID

Reputation: 4014

SELECT  P.cid, COUNT ( P.pid ) AS CountP
FROM    Patient P
GROUP BY    P.cid
compute max(count(p.pid))

Upvotes: 0

virtualeyes
virtualeyes

Reputation: 11237

Try with the following:
max(count(P.pid))

Upvotes: 0

Related Questions