0 votes
1 view
in SQL by (2.3k points)

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.

1 Answer

0 votes
by (4.3k points)

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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Apr 26, 2020 in SQL by Sudhir_1997 (53.7k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...