X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 128899FB4B2 for ; Fri, 1 Sep 2006 11:17:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 90383-08-3 for ; Fri, 1 Sep 2006 11:11:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.203]) by postgresql.org (Postfix) with ESMTP id BF97D9FB512 for ; Fri, 1 Sep 2006 10:31:50 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id 14so668382nzn for ; Fri, 01 Sep 2006 06:31:49 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=EEffZTzMvhzO9bWEaYqf7q0pTEfQNSG8zrzEX6r4k5WMzEd+8uwzwSQeC4extM1epUIeB/Qwu0J1dlwvDhpzREJ58k9rAu/tWNK8eGEGMO/pYHFhC//Y5J2vqxK7c/MBTg1lFVvtoEBoWf+FyZpkp4Cxv1vkEnndgszMEbdNt1I= Received: by 10.65.211.13 with SMTP id n13mr2646595qbq; Fri, 01 Sep 2006 06:31:48 -0700 (PDT) Received: by 10.65.61.16 with HTTP; Fri, 1 Sep 2006 06:31:48 -0700 (PDT) Message-ID: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com> Date: Fri, 1 Sep 2006 10:31:48 -0300 From: "Walter Cruz" To: pgsql-sql@postgresql.org Subject: help with pagila MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_34199_10952426.1157117508250" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.424 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/1 X-Sequence-Number: 26212 ------=_Part_34199_10952426.1157117508250 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all. I'm with a little doubt. I'm testing the pagila (the postgres port of mysql sakila sample). Well, I was trying to translate the query: select film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, group_concat(concat(actor.first_name,_utf8' ',actor.last_name) separator ',') AS actors from category inner join film on(category.category_id = film.category_id) inner join film_actor on(film.film_id = film_actor.film_id) inner join actor on(film_actor.actor_id = actor.actor_id) group by film.film_id; That cant be find here: http://www.stardata.it/articoli_en/mysql_sample_db_articoli_en.html I read a comment by David Fetter on a blog about group concat. I'm following his example: select film.title AS title, array_to_string(array_accum(actor.first_name || ' ' || actor.last_name),',') AS actors from film inner join film_actor on film.film_id = film_actor.film_id inner join actor on film_actor.actor_id = actor.actor_id GROUP BY film.title But, when I add another column on select, like, film_description, I get the following error: "ERROR: column "film.description" must appear in the GROUP BY clause or be used in an aggregate function" If I put that column on GROUP BY everything works ok. But I want understant why do I need to do that. Can someone teach me, please? []'s - Walter ------=_Part_34199_10952426.1157117508250 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all. I'm with a little doubt.

I'm testing the pagila (the postgres port of mysql sakila sample).

Well, I was trying to translate the query:

select
     film.film_id AS FID,
     film.title
 AS title,
     film.description AS description,
     category.name AS category,
     film.rental_rate AS price,
     film.length AS length,
     film.rating AS rating,

     group_concat(concat(actor.first_name,_utf8' ',actor.last_name) separator ',') AS actors 
from
     category
     inner join film on(category.category_id = film.category_id)
     inner join film_actor on(film.film_id
 = film_actor.film_id)
     inner join actor on(film_actor.actor_id = actor.actor_id)
group by
    film.film_id;

That cant be find here: http://www.stardata.it/articoli_en/mysql_sample_db_articoli_en.html

I read a comment by David Fetter on a blog about group concat. I'm following his example:

select
     film.title AS title, 
     array_to_string(array_accum( actor.first_name || ' ' || actor.last_name),',') AS actors
from
     film
     inner join film_actor on film.film_id = film_actor.film_id
     inner join actor on film_actor.actor_id = actor.actor_id
    GROUP BY film.title
    


But, when I add another column on select, like, film_description, I get the following error:

"ERROR:  column "film.description" must appear in the GROUP BY clause or be used in an aggregate function"

If I put that column on GROUP BY everything works ok. But I want understant why do I need to do that. Can someone teach me, please?

[]'s
- Walter
------=_Part_34199_10952426.1157117508250--