From: Walter Cruz <walter.php@gmail.com>
To: pgsql-sql@postgresql.org
Subject: help with pagila
Date: Fri, 1 Sep 2006 10:31:48 -0300
Message-ID: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: walter.php@gmail.com
Subject: Re: help with pagila
In-Reply-To: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox