From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Ntina Papadopoulou <ntina23gr(at)freemail(dot)gr> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: select id,count(imdb_id) problem |
Date: | 2006-04-10 15:39:23 |
Message-ID: | 20060410153923.GA18355@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Mon, Apr 10, 2006 at 09:25:58 +0300,
Ntina Papadopoulou <ntina23gr(at)freemail(dot)gr> wrote:
> select imdb_id,count(imdb_id) from "Movies" where id<50 group by imdb_id;
> imdb_id | count
> ---------+-------
> 267248 | 3
> 343660 | 2
> 298203 | 4
> 315733 | 9
> 322259 | 12
> 411705 | 12
> 268978 | 1
> 289043 | 6
>
> select "Title",imdb_id,count(imdb_id) from "Movies" where id<15 group by
> imdb_id;
> ERROR: column "Movies.Title" must appear in the GROUP BY clause or be
> used in an aggregate function
To do this in Postgres you need to join the output of the counting select
back against the movie table, joining on imdb_id. At this point Postgres
doesn't understand that imdb is a candidate key of movies so that it makes
sense to include the title column, because it will be well defined.
The query would look something like the following untested query:
SELECT
a."Title", a.imdb_id, b.cnt
FROM "Movies" a,
(SELECT
imdb_id, count(*) AS cnt
FROM "Movies"
GROUP BY imdb_id)
AS b
WHERE
a.imdb_id = b.imdb_id
ORDER BY a.imdb_id
;
From | Date | Subject | |
---|---|---|---|
Next Message | operationsengineer1 | 2006-04-10 18:00:14 | Re: PostgreSQL a slow DB? |
Previous Message | Ntina Papadopoulou | 2006-04-10 06:25:58 | Re: select id,count(imdb_id) problem |