Most Efficient way to write HAVING condition

Question

I am currently completing the More JOIN operations tutorial of sqlzoo and encountered the following code as the answer to #12:

SELECT yr,COUNT(title) 
FROM movie 
JOIN casting 
JOIN actor   
ON actorid=actor.id AND movie.id=movieid
WHERE name='John Travolta'
GROUP BY yr

HAVING COUNT(title)=
(SELECT MAX(c) FROM
(SELECT yr, COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)

Is there not a more concise way to express this code?

"for any year in which he made more than 2 movies. " so `HAVING COUNT(title)>2` — Jen R, Jun 18 '15 at 18:43

score 2 · Answer 1 · answered Jun 18 '15 at 18:43

2

Yes. If I understand the quesiton correctly it is much more simple.

SELECT yr,COUNT(title) 
   FROM movie JOIN casting ON movie.id=movieid
        JOIN 
        actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title) > 2

answered Jun 18 '15 at 18:43

Karl Kieninger

8,841
2
33
49

score 1 · Answer 2 · answered Jun 18 '15 at 18:56

It looks like the original site's suggested answer does not match the question they were asking.

Best answer to what they asked:

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)>2

Their answer was:

SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=
(SELECT MAX(c) FROM (SELECT yr,COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t

)

But this actually answers a different question, which is: "For the year in which he made the most movies, what year was that and how many movies did he make?"

Most Efficient way to write HAVING condition

2 Answers2