Oracle: combining subqueries and aggregate functions

July 18, 2022

I am new to databases and Oracle and I am supposed to find in a table filled with all soccer teams of a World Cup all nations, which have the biggest amount of midfield players. This is the table structure:

Player(PlayerID<PK>,Role,Nationname)

I admit I am not really getting how subqueries can work together with aggregate functions. I tried to find the max amount of midfields in the table like this, but I am not getting any output:

SELECT DISTINCT nationname, count(role)
FROM player
GROUP BY nationname
HAVING COUNT(role) = (SELECT max(count(role))
                   FROM player
                   WHERE role = 'midfield'
                   GROUP BY nationname);

I also tried with a subquery in the FROM clause (I have seen on this site that many use this type of subquery) like this:

SELECT nationname, MAX(midfield) FROM 
( SELECT nationname, COUNT(role) midfield
  FROM player
  WHERE role = 'midfield'
  GROUP BY nationname) test
  GROUP BY nationname;

Ouput:

With this query I am getting closer to the task but I cannot filter further the results in order to only get the Honduras-team.
What can i do for it?
I would really appreciate any hint or help!

>Solution :

In your inner query you select and group and then you can select first row when you order by counted roles.

select *
from (SELECT nationname, COUNT(role) midfield
      FROM player
      WHERE role = 'midfield'
      GROUP BY nationname)
where rownum = 1
order by midfield desc

If there will be more than one country that has the same max number of players in midfield position then use this:

SELECT nationname, COUNT(role) midfield
FROM player
WHERE role = 'midfield'
GROUP BY nationname
having count(role) in (select max(count(role)) 
                       from player 
                       where role = 'midfield' 
                       group by nationname)

DEMO