Re: select id,count(imdb_id) problem - Mailing list pgsql-novice

On Mon, Apr 10, 2006 at 09:25:58 +0300,
  Ntina Papadopoulou <ntina23gr@freemail.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
;