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 f0JNTJ067711 for ; Fri, 19 Jan 2001 18:29:19 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0JNI0x82517 for pgsql-sql@postgresql.org; Fri, 19 Jan 2001 18:18:00 -0500 (EST) (envelope-from news) From: Jussi =?iso-8859-1?Q?Vainionp=E4=E4?= X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Trouble with subqueries Date: Sat, 20 Jan 2001 01:18:44 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 33 Message-ID: <3A68CB54.224A7BE7@cc.hut.fi> References: <11131831464.20010119180542@vpcit.ru> 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.5 [en] (Win95; I) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200101/267 X-Sequence-Number: 936 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?