Thread: select id,count(imdb_id) problem
Hello community! When I type a query in postgresql, like select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by imdb_id; it says: column "Movies.id" must appear in the GROUP BY clause or be used in an aggregate function Where is the error? What is the right spelling of this query? ____________________________________________________________________ http://www.freemail.gr - δωρεάν υπηρεσία ηλεκτρονικού ταχυδρομείου. http://www.freemail.gr - free email service for the Greek-speaking.
am 10.04.2006, um 8:28:12 +0300 mailte Ntina Papadopoulou folgendes: > Hello community! > > When I type a query in postgresql, like > > select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by > imdb_id; > > it says: column "Movies.id" must appear in the GROUP BY clause or be used > in an aggregate function > Where is the error? The column 'Movies.id' isn't in the group by clause. > What is the right spelling of this query? select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by id,imdb_id; HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
O/H A. Kretschmer έγραψε: > am 10.04.2006, um 8:28:12 +0300 mailte Ntina Papadopoulou folgendes: > >> Hello community! >> >> When I type a query in postgresql, like >> >> select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by >> imdb_id; >> >> it says: column "Movies.id" must appear in the GROUP BY clause or be used >> in an aggregate function >> Where is the error? >> > > The column 'Movies.id' isn't in the group by clause. > > > >> What is the right spelling of this query? >> > > select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by id,imdb_id; > > > HTH, Andreas > Thnx a lot Andreas, but the query you gave me has not the desired result. when i select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by imdb_id,id order by id asc; what I get is id | imdb_id | count ----+---------+------- 1 | 315733 | 1 2 | 315733 | 1 3 | 315733 | 1 4 | 315733 | 1 5 | 315733 | 1 6 | 315733 | 1 7 | 315733 | 1 8 | 315733 | 1 9 | 315733 | 1 The desired result is something like id | imdb_id | count ----+---------+------- 1 | 315733 | 9 10 | 335753 | 1 11 | 320000 | 15 etc. ____________________________________________________________________ http://www.freemail.gr - ������ �������� ������������ ������������. http://www.freemail.gr - free email service for the Greek-speaking.
am 10.04.2006, um 8:52:11 +0300 mailte Ntina Papadopoulou folgendes: > O/H A. Kretschmer ????????????: > >am 10.04.2006, um 8:28:12 +0300 mailte Ntina Papadopoulou folgendes: > > > >>Hello community! > >>When I type a query in postgresql, like > >>select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by > >>imdb_id; There is something wrong. You say 'where id<10' but your desired result: > > The desired result is > something like > id | imdb_id | count > ----+---------+------- > 1 | 315733 | 9 > 10 | 335753 | 1 > 11 | 320000 | 15 contains id's larger then 10. Sorry, i can't understand your question. -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Mon, Apr 10, 2006 at 08:52:11 +0300, Ntina Papadopoulou <ntina23gr@freemail.gr> wrote: > >>select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by > >>imdb_id; > > The desired result is > something like > id | imdb_id | count > ----+---------+------- > 1 | 315733 | 9 > 10 | 335753 | 1 > 11 | 320000 | 15 > etc. select imdb_id,count(imdb_id) from "Movies" where id<10 group by imdb_id; might be closer to what you want. However, the example that you gave doesn't match that exactly. Maybe if you described what you are trying to do in more detail, people could give you some better suggestions.
O/H A. Kretschmer έγραψε: > am 10.04.2006, um 8:52:11 +0300 mailte Ntina Papadopoulou folgendes: > >> O/H A. Kretschmer ????????????: >> >>> am 10.04.2006, um 8:28:12 +0300 mailte Ntina Papadopoulou folgendes: >>> >>> >>>> Hello community! >>>> When I type a query in postgresql, like >>>> select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by >>>> imdb_id; >>>> > > There is something wrong. You say 'where id<10' but your desired result: > > >> The desired result is >> something like >> id | imdb_id | count >> ----+---------+------- >> 1 | 315733 | 9 >> 10 | 335753 | 1 >> 11 | 320000 | 15 >> > > contains id's larger then 10. > > > Sorry, i can't understand your question. > ok! I want the select id,imdb_id,count(imdb_id) from "Movies" where id<50 group by imdb_id; give me something like id | imdb_id | count ----+---------+------- 1 | 315733 | 9 10 | 335753 | 1 11 | 320000 | 40 ok now? ____________________________________________________________________ http://www.freemail.gr - ������ �������� ������������ ������������. http://www.freemail.gr - free email service for the Greek-speaking.
O/H Bruno Wolff III έγραψε: > On Mon, Apr 10, 2006 at 08:52:11 +0300, > Ntina Papadopoulou <ntina23gr@freemail.gr> wrote: > >>>> select id,imdb_id,count(imdb_id) from "Movies" where id<10 group by >>>> imdb_id; >>>> >> The desired result is >> something like >> id | imdb_id | count >> ----+---------+------- >> 1 | 315733 | 9 >> 10 | 335753 | 1 >> 11 | 320000 | 15 >> etc. >> > > select imdb_id,count(imdb_id) from "Movies" where id<10 group by imdb_id; > might be closer to what you want. > However, the example that you gave doesn't match that exactly. > Maybe if you described what you are trying to do in more detail, people could > give you some better suggestions. > > Thnx a lot! select imdb_id,count(imdb_id) from "Movies" where id<50 group by imdb_id; did the work But just for learning purposes, why select id,"Title" from "Movies" where id<15; works! id | Title ----+------------------ 1 | 21 Grams 2 | 21 Grams 3 | 21 Grams 4 | 21 Grams 5 | 21 Grams 6 | 21 Grams 7 | 21 Grams 8 | 21 Grams 9 | 21 Grams 10 | 28 Days Later... 11 | 28 Days Later... 12 | 28 Days Later... 13 | 28 Days Later... 14 | 28 Days Later... 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 ____________________________________________________________________ http://www.freemail.gr - ������ �������� ������������ ������������. http://www.freemail.gr - free email service for the Greek-speaking.
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
;