Can I do a max(count(*)) in SQL?

Question

Here's my code:

select yr,count(*) from movie join casting on casting.movieid=movie.id join actor on casting.actorid = actor.id where actor.name = 'John Travolta' group by yr; 

Here's the question:

Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

Here's the table structure:

movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) 

This is the output I am getting:

yr      count(*) 1976    1 1977    1 1978    1 1981    1 1994    1 -- etc. 

I need to get the rows for which count(*) is max. How do I do this?

Answer 1

Use:

  SELECT m.yr,           COUNT(*) AS num_movies     FROM MOVIE m     JOIN CASTING c ON c.movieid = m.id     JOIN ACTOR a ON a.id = c.actorid                 AND a.name = 'John Travolta' GROUP BY m.yr ORDER BY num_movies DESC, m.yr DESC 

Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the same count, the m.yr will place the most recent year at the top... until the next num_movies value changes.

Can I use a MAX(COUNT(*)) ?

---

No, you can not layer aggregate functions on top of one another in the same SELECT clause. The inner aggregate would have to be performed in a subquery. IE:

SELECT MAX(y.num)   FROM (SELECT COUNT(*) AS num           FROM TABLE x) y 

Answer 2

Just order by count(*) desc and you'll get the highest (if you combine it with limit 1)