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