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?
12 Answers
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
Just order by count(*) desc and you'll get the highest (if you combine it with limit 1)
SELECT * from
(
SELECT yr as YEAR, COUNT(title) as TCOUNT
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr
order by TCOUNT desc
) res
where rownum < 2
This question is old, but was referenced in a new question on dba.SE. I feel the best solutions haven't been provided. Plus, there are new, faster options.
Question in the title
Can I do a
max(count(*))in SQL?
Yes, you can achieve that by nesting an aggregate function in a window function:
SELECT m.yr, count(*) AS movie_count
, max(count(*)) OVER () AS max_ct
FROM casting c
JOIN movie m ON c.movieid = m.id
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP BY m.yr
ORDER BY count(*) DESC;
db<>fiddle here
That's standard SQL. Postgres introduced it with version 8.4 (released 2009-07-01, before this question was asked. Other RDBMS should be capable of the same.
Consider the sequence of events in a SELECT query:
Possible downside: window functions do not aggregate rows. You get all rows left after the aggregate step. Useful in some queries, but not ideal for this one.
To get one row with the highest count, you can use ORDER BY ct LIMIT 1:
SELECT c.yr, count(*) AS ct
FROM actor a
JOIN casting c ON c.actorid = a.id
WHERE a.name = 'John Travolta'
GROUP BY c.yr
ORDER BY ct DESC
LIMIT 1;
Using only basic SQL features, available in any halfway decent RDBMS - the LIMIT implementation varies:
Or you can get one row per group with the highest count with DISTINCT ON (only Postgres):
