pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jussi Vainionpää <jjvainio@cc.hut.fi>
To: pgsql-sql@postgresql.org
Subject: Re: Trouble with subqueries
Date: Sat, 20 Jan 2001 01:18:44 +0200
Message-ID: <3A68CB54.224A7BE7@cc.hut.fi> (raw)
References: <11131831464.20010119180542@vpcit.ru>

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?





view thread (4+ messages)  latest in thread

Message-ID: <3A68CB54.224A7BE7@cc.hut.fi>
Permalink:  ../3A68CB54.224A7BE7@cc.hut.fi/
Also on:    postgresql.org/message-id/3A68CB54.224A7BE7@cc.hut.fi

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jjvainio@cc.hut.fi
  Subject: Re: Trouble with subqueries
  In-Reply-To: <3A68CB54.224A7BE7@cc.hut.fi>
  References: <11131831464.20010119180542@vpcit.ru> <3A68CB54.224A7BE7@cc.hut.fi>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox