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