Yury Don wrote:
> If I understand correctly it must looks like this:
> SELECT name, length,
> (SELECT count(*)
> FROM ratings
> WHERE rating='5'
> and rating.name=movies.name) as fives
> FROM movies
> WHERE name=rname;
This results in several rows for each movie, which can be fixed by using select
distint, but I don't quite understand why that happens. Any ideas?
The method suggested by Tomas Berndtsson involving an intermediate view works
nicely too. But this was not quite what I was looking for, as I would like to
have all the movies in the list, also the ones with no ratings. The fives column
should just be zero for those.
I though about creating a view of the union of the movies table and these
results and then doing select max(fives) group by name; from that view, but it
seems that views with unions are not allowed.
But I did find a solution:
SELECT movies.name, movies.length, COUNT(CASE WHEN ratings.name=movies.name AND rating='5' THEN true
END)AS fives FROM ratings, movies GROUP BY movies.name, movies.length;
But I don't quite understand why I need to have movies.length in the GROUP BY
-clause?