CODEWITHSUNDEEP
mysql
Regnum
Regnum

Reputation: 33

Retrieve the max values from different rows

Sorry, the title might be a bit hard to understand. I'm studying MySQL at school but right now I'm stuck in an exercise and I'm not able to find the solution anywhere around (although I'm 100% sure it's out there, I just can't find it).

Anyway, I have a table with some medical departments that go by the name of Psychiatry, Surgeon and Dermatologist. Both the surgeon and psychiatry areas have 2 medics in them, and dermatologist has just one. I'm supposed to get the name of the area with the most medics, simple as that. The problem I have is that I can't get BOTH areas to appear, just one of them or all of them. This is my code:

select speciality, count(*) 
from medics
group by speciality
order by count(*);

I've also tried with:

select max(total), speciality
from
(select speciality, count(*) as 'total' from medics
group by speciality
order by count(*)) 
as test
group by speciality;

EDIT: This is the table where I must take the data from: 

cod_hospital dni      apellidos               epecialidad
           4 22233311 Martínez Molina, Gloria PSIQUIATRA
           2 22233322 Tristán García, Ana     CIRUJANO
           2 22233333 Martínez Molina, AndrEs CIRUJANO
           4 33222111 Mesa del Castillo, Juan DERMATOLOGO
           1 66655544 Castillo Montes, Pedro  PSIQUIATRA

And this is the expected result. "1 - DERMATOLOGO" result shouldn't appear there since that's not the speciality with the most medics:

max(total) especialidad
         2 CIRUJANO
         2 PSIQUIATRA

Upvotes: 3

Views: 60

Answers (1)

Strawberry
Strawberry

Reputation: 33945

E.g.:

SELECT a.* 
  FROM 
     (SELECT especialidad,COUNT(*) total FROM medics GROUP BY especialidad) a 
  JOIN 
     (SELECT COUNT(*) total FROM medics GROUP BY especialidad ORDER BY total DESC LIMIT 1) b 
    ON b.total = a.total;

Yes. It really can be that crude sometimes.

Upvotes: 5

Related Questions