Oracle: combining subqueries and aggregate functions-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

If you use rank analytic function (why not row_number? Because two (or more) teams can have the same number of midfield players), then you could

with temp as
  -- rank them first, by count of midfield players in descending order
  (select nationname, 
          rank() over (order by count(*) desc) rnk, 
          count(*) cnt
   from player
   where role = 'midfield'
   group by nationname
  )
-- now, fetch nation which ranks as the highest
select nationname, cnt
from temp
where rnk = 1;