PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query

From: DaNieL <daniele(dot)pignedoli(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query
Date: 2009-05-04 16:27:30
Message-ID: 8b7677d2-5580-4e48-91ff-130b9fe72dda@p4g2000vba.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message justin 2009-05-04 16:29:37 Re: Adding automatic backup of a DB
Previous Message Pavel Stehule 2009-05-04 15:28:22 Re: How to return SETOF RECORD?