Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux