SubSelect

I'm trying to do a subselect in some tables and always its return to me an error ask me to check the manual.

My subselect:

SELECT m.movie_title_id, m.media_id, t.MEDIA_ID, count(t.MEDIA_ID), count(m2.media_id)
FROM member_media_rental t, media m
inner join select count(m2.media_id) from media m2 where m2.MOVIE_TITLE_ID = m.MOVIE_TITLE_ID
and m.media_id = t.MEDIA_ID
group by t.MEDIA_ID;

Thanks all.