On Mon, May 4, 2009 at 12:27 PM, DaNieL <daniele.pignedoli@gmail.com> wrote:
> Hi guys, this is my first approach to postgresql..
>
> Well, lets say that i have 3 tables: orders, customer, and order_item.
> The tables are really simple:
>
> ---
> CREATE TABLE customer (
> id integer NOT NULL,
> name character(50)
> );
> ---
> CREATE TABLE orders (
> id integer NOT NULL,
> id_customer integer,
> code character(10)
> );
>
> ---
> CREATE TABLE order_item (
> id integer NOT NULL,
> id_order integer,
> text text,
> price numeric(5,2)
> );
> ---
>
> Every id in every table is a PRIMARY KEY, UNIQUE, NOT NULL and serial
> type..
> The query that i have problem with is:
> ---
> SELECT
> orders.code,
> customer.name,
> SUM(order_item.price)
> FROM
> orders
> INNER JOIN customer ON (customer.id = orders.id_customer)
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> GROUP BY orders.id
> ---
>
> thet report the error:
> ---
> ERROR: column "orders.code" must appear in the GROUP BY clause or be
> used in an aggregate function
> ---
>
> ...why?
> My goal is to retrieve something like
> 00001 | Mary | 439.43
> 00002 | Steve | 432.32
> 00003 | Abigail | 243.32
> 00004 | Steve | 156.23
> becose users can have many orders, and every order can have many
> items.
>
> Please, help me.. i really dont understand the error, becose if i
> group for the orders.code, the error point at the customer.name:
>
> ---
> ERROR: column "customer.name" must appear in the GROUP BY clause or
> be used in an aggregate function
> ---
>
> and if i add the customer.name in the GROUP BY statement, it works
> ---
> 0000000002 | Steve | 32
> 0000000001 | Abigail | 69.77
> 0000000003 | Abigail | 25.93
> ---
> .. but, why?
> Isn't the GROUP BY orders.id statement enaught?
> Why the costumers.name is needed?
>
> Sorry, i know that this maybe is a basically problem, but i come from
> mysql.. and in mysql that query works...
Blame the sql standard, and logic. While the unique constraint on id
means that there is no harm in omitting the 'group by name', it is
easy to write queries that allow ambiguous results...for example if
there was no unique constraint on name.
merlin