Sunday, 26 July 2015

Multi Column Sub query scenario

Display the details of hero who has acted in more than 2 movies in  each year.



HERO_ID =1 has more than 2 movies only in 2001.
HERO_ID=2 has more than 2 movies in every year.

SELECT HERO_ID FROM 
(
SELECT HERO_ID,RELEASE_YEAR FROM MOVIES GROUP BY HERO_ID,RELEASE_YEAR HAVING COUNT(*)>2
)
GROUP BY HERO_ID

HAVING COUNT(DISTINCT RELEASE_YEAR)>1

No comments:

Post a Comment