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.

Answer 1

To using max(count(*)) in SQL, you need to write this code for more explanation

SELECT m.sh,

 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 = 'Ajay' 

 GROUP BY m.sh

 ORDER BY num_movies DESC, m.sh DESC

 

Here we use, order by num_movies DESC, it will show the highest value at the top

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

No, we can’t use a MAX(COUNT(*) and we can not layer aggregate functions on top of one another in the same SELECT clause. 

In a subquery, the inner aggregate would have to be performed. 

i.e

SELECT MAX(b.num) FROM (SELECT COUNT(*) AS num FROM TABLE a) b