I have the following two tables:
create table movies ( name varchar(80), info varchar(80), length int, primary key (name)
);
create table ratings ( name varchar(80), userid varchar(10), rating char(1), foreign key (name) references movies,
primarykey(name, userid)
);
The tables contain movies and users' ratings of the movies.
I would like to get a listing of all the movies along with how many
users have given the movie some particular rating. The first solution
that I came up with was this:
SELECT name, length, fives FROM movies, (SELECT name as rname, count(*) as fives FROM ratings
WHERE rating='5' GROUP BY name) WHERE name=rname;
but in PostgreSQL 7 it just gives me this error message:
ERROR: parser: parse error at or near "("
I have previously used similar queries in Oracle where they have worked,
so it would seem to me that PostgreSQL doesn't support subselects after
all despite all the claims.
Am I doing something wrong or/and is there some another way of making
this query that would work in PostgreSQL?