Subquery problem

From: Jussi Vainionpää <jjvainio_at_cc.hut.fi>
Date: Fri, 19 Jan 2001 05:39:42 +0200
Message-ID: <3A67B6FE.315DB4C@cc.hut.fi>

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,
  primary key(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 this gives me this error message: 'ERROR: parser: parse error at or near "("' in PostgreSQL 7. I have previously used similar queries in Oracle where they have worked, so it would seem 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? Received on Thu Jan 18 2001 - 21:39:42 CST