Re: select id,count(imdb_id) problem

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
;