sqloracle-databaseoracle11g

How to fix 'ORA-00978: nested group function without GROUP BY'

Alright, I know how group by's work but this I keep getting this message and I have no idea what I did wrong.

What I should do is: Give the name and number of presidents of the party that has had the largest number of presidents.

select p1.party, count(p1.pres_name)
from president p1
group by p1.party, p1.pres_name
having 
   count(p1.pres_name) =
(select max(count(p2.pres_name))from president p2 where p1.part = p2.party)
Solution

  • Use window functions:

    select party, num_presidents
from (select p.party, count(*) as num_presidents,
             rank() over (order by count(*) desc) as seqnum
      from president p
      group by p.party,
     ) p
where seqnum = 1;