Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0JCaK003102 for ; Fri, 19 Jan 2001 07:36:20 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0J7xG083510 for ; Fri, 19 Jan 2001 02:59:16 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0J7Yo468981 for pgsql-sql@postgresql.org; Fri, 19 Jan 2001 02:34:50 -0500 (EST) (envelope-from news) From: Jussi =?iso-8859-1?Q?Vainionp=E4=E4?= X-Newsgroups: comp.databases.postgresql.sql Subject: Trouble with subqueries Date: Fri, 19 Jan 2001 09:34:50 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 41 Message-ID: <3A67EE1A.B7319298@cc.hut.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.7 [en] (Win98; I) X-Accept-Language: fi,en To: pgsql-sql@postgresql.org X-Archive-Number: 200101/254 X-Sequence-Number: 923 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 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?