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

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

In response to DaNieL <daniele.pignedoli@gmail.com>:

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

Because the database serer has no way to know that it can do that.
You're grouping by order.id, because you _know_ that doing so will
capture what you want.  PostgreSQL has no way of knowing this, what
it _does_ know is that there are certain possible table layouts in
which doing without grouping by both statements could lead to
incorrect results.

Someone with a more expensive education than me may jump in with
more details, but I'm pretty sure this behaviour is mandated by
the SQL standard.  I mean, PostgreSQL _could_ behave the same way
as MySQL, but he designers chose this way because it's more
reliable.

> Sorry, i know that this maybe is a basically problem, but i come from
> mysql.. and in mysql that query works...

That's a shame, really ... that MySQL allows you to execute a query
that could produce indeterminate results.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Mon, 4 May 2009 09:27:30 -0700 (PDT)
DaNieL <daniele.pignedoli@gmail.com> wrote:

[snip]

> 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

> Sorry, i know that this maybe is a basically problem, but i come
> from mysql.. and in mysql that query works...

You have to add explicitly all the columns not in aggregates.
Postgresql is a bit stricter than mysql but it generally saves you a
lot of debugging later.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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

Yes, that query works in mysql, but only in mysql... and probably not in
any other db anywhere.  It is not standard sql.  My guess is that mysql
is "helping" you out by adding the customer.name for you... but maybe
not?  Maybe its returning something else?  Too bad it lets you write
confusing questions.

The general rule is, when you use a group by, all fields in the select
statement must either be an aggregate function, or listed in the group
by.  All of them.

Think about a temp table like:

id,  name,  value
---------------
1, Mary,  20
2, Mary,  30
3, Abigale, 10

if you:
select name, sum(value) from temp group by name

you'll get the sum of value for each name:
Mary 50
Abigale 10

This, then, makes no sense:
select id, name, sum(value) from temp group by name

You said group by name, which means you only want ONE Mary is your
result set.  But Mary has two id's.  So in the above query which result
set is right?

1, Mary 50
3, Abigale 10

or
2, Mary 50
3, Abigale 10

Neither... it doesn't make sense.  If you add a min(id), the it makes sense:

select min(id), name, sum(value) from temp group by name

and you should get:
1, Mary 50
3, Abigale 10

If you want to see all the ID's Mary has then you need to put it in the
group by:


select id, name, sum(value) from temp group by name, id

and get:
1, Mary,  20
2, Mary,  30
3, Abigale, 10

A group by is saying:  "when this list of fields match, sum them all up"

so when you have "group by name", then all the Mary's will be summed up,
and all the Abigale's will be summed up, and you'll get One Mary, and
One Abigale.

("summed up" really means "aggregated up", if that's a word)


-Andy

On Mon, May 04, 2009 at 09:27:30AM -0700, DaNieL 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)

Use TEXT instead of char(n) or varchar(n) unless n is actually a data
integrity constraint, i.e. if the data can't be right unless the
length matches that specification.

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

Is there something unclear about that error message?

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

It's because PostgreSQL doesn't just assume it knows better than you
do and take a guess at what you might have meant. :)

> Sorry, i know that this maybe is a basically problem, but i come
> from mysql.. and in mysql that query works...

For small values of, "works." ;)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

To get a postgresql behavior similar to mysql's you need to use distinct on:

select distinct on (a) a,b,c from sometable; (or something like that)

On Mon, 2009-05-04 at 12:30 -0500, Andy Colson wrote:
> Yes, that query works in mysql, but only in mysql... and probably not in
> any other db anywhere.  It is not standard sql.  My guess is that mysql
> is "helping" you out by adding the customer.name for you... but maybe
> not?  Maybe its returning something else?  Too bad it lets you write
> confusing questions.

Section 4.18 of SQL200n, "Functional Dependencies", shows some
interesting ways that the DBMS can make the proper inferences (I think
this is an optional feature, so I don't think PostgreSQL violates the
standard here).

I'm not sure if what DaNieL is asking for is actually covered by this
feature, because it would need to infer the function dependencies:

orders.id -> orders.code
orders.id -> customer.name

and the second one needs to be inferred through a couple steps, and that
may be more sophisticated than what the standard asks for. It can be
done though, because:

orders.id -> orders.id_customer (implied by key on orders.id)
orders.id_customer = customer.id (from join condition)
customer.id -> customer.name (implied by key on customer.id)

Therefore:

orders.id -> customer.name

So he's not asking for anything ridiculous, and it looks to me like he's
supported by the standard (although I'm not an expert on the SQL
standard). However, sometimes it's a good idea to be a little more
explicit in the queries, just for the sake of readability.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> Section 4.18 of SQL200n, "Functional Dependencies", shows some
> interesting ways that the DBMS can make the proper inferences (I think
> this is an optional feature, so I don't think PostgreSQL violates the
> standard here).

Just for the record, this is something that was added in SQL:99 ---
our behavior conforms to the letter of earlier SQL versions.  I think
we have a TODO item to add at least some support for allowing implicit
GROUP BY using functional dependencies, but it's kind of a worrisome
thing.  I don't know of any other part of the SQL spec whereby ALTER
TABLE DROP CONSTRAINT could turn a formerly semantically legal query
into an illegal query.  Could have some unpleasant implications for the
behavior of prepared statements.

(Also, I'll bet a lot of money that mysql has not implemented this
feature according to spec.  The last I heard, as long as you have a
GROUP BY they just blithely assume you know what you're doing and
didn't write a query whose results are ambiguous.)

            regards, tom lane

Hi guyst.. thanks for the replies, really, them make me suppose that
all what i've learned of sql from mysql can be wrong..

But still i have some trouble to understand the functionality of the
orders example.
My first goal is to retrieve every order, the customer name, and the
total of the idems per order.. so (from my point of view) i *dont*
need and *dont* ant to group by the costumer name, becose every
costumer can have many orders...
and, as i understand the query:
---
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, customer.name
---
it *try* to group by the customer name, but *fail* becose the GROUP
priority is at the order's code.. how much am i far from it?
but aswell i cant understand why i must group for the costumer name,
when i dont want and dont need it to be *unique*

So, lets suppose that i want to retrieve the total price of every item
bought by every customer, i'll start with this query:
---
SELECT
 customer.name,
 SUM(order_item.price)
FROM
 customer
 INNER JOIN orders ON (orders.id_customer = customer.id)
 INNER JOIN order_item ON (order_item.id_order = orders.id)
GROUP BY customer.id
---
but still, SQL throw an error, and i must add the costumer.name in the
group clause.
And still, that will be *ignored* becose the *main* GROUP BY is
customer.id.. and the query works.

So, suppose i have thosa data:
customer
id | name
1  | Abigail
2  | Steve

orders:
id | id_customer | code
1  | 1                 | 0001
2  | 2                 | 0002
3  | 1                 | 0003

order_item:
id | id_order | price  |
1  | 1           | 21.23 |
2  | 1           | 21.32 |
3  | 2           | 21.23 |
4  | 2           | 21.32 |
5  | 3           | 21.23 |
6  | 3           | 21.32 |
So, Abigail have 2 orders, steve 1, every order have 2 items.

If i would to retrieve the number of orders, the number of total items
bought (total sum of every order) for every customer, the query shuold
be like that:
---
SELECT
 customer.name,
 COUNT(orders.id),
 (
   SELECT
   COUNT(order_item.id)
  FROM
   orders
   INNER JOIN order_item ON (order_item.id_order = orders.id)
  WHERE orders.id_customer = customer.id
  GROUP BY orders.id_customer
 ) AS total_items
FROM
 customer
 INNER JOIN orders ON (orders.id_customer = customer.id)
GROUP BY customer.id, customer.name
---

Right?

On May 4, 9:27 am, DaNieL <daniele.pigned...@gmail.com> wrote:
> Sorry, i know that this maybe is a basically problem, but i come from
> mysql.. and in mysql that query works...

if there's only one name per order, just put a min or max around the
second col.  as you know by now, all columns that are not GROUPed BY
must be in an aggregate function.

SELECT
 orders.code,
 MIN(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

daniel

what happens when you implement the INNER JOIN statement and remove group by
?

Martin
______________________________________________
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité

This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> From: daniele.pignedoli@gmail.com
> Subject: Re: [GENERAL] PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query
> Date: Tue, 5 May 2009 00:10:56 -0700
> To: pgsql-general@postgresql.org
>
> Hi guyst.. thanks for the replies, really, them make me suppose that
> all what i've learned of sql from mysql can be wrong..
>
> But still i have some trouble to understand the functionality of the
> orders example.
> My first goal is to retrieve every order, the customer name, and the
> total of the idems per order.. so (from my point of view) i *dont*
> need and *dont* ant to group by the costumer name, becose every
> costumer can have many orders...
> and, as i understand the query:
> ---
> 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, customer.name
> ---
> it *try* to group by the customer name, but *fail* becose the GROUP
> priority is at the order's code.. how much am i far from it?
> but aswell i cant understand why i must group for the costumer name,
> when i dont want and dont need it to be *unique*
>
> So, lets suppose that i want to retrieve the total price of every item
> bought by every customer, i'll start with this query:
> ---
> SELECT
> customer.name,
> SUM(order_item.price)
> FROM
> customer
> INNER JOIN orders ON (orders.id_customer = customer.id)
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> GROUP BY customer.id
> ---
> but still, SQL throw an error, and i must add the costumer.name in the
> group clause.
> And still, that will be *ignored* becose the *main* GROUP BY is
> customer.id.. and the query works.
>
> So, suppose i have thosa data:
> customer
> id | name
> 1 | Abigail
> 2 | Steve
>
> orders:
> id | id_customer | code
> 1 | 1 | 0001
> 2 | 2 | 0002
> 3 | 1 | 0003
>
> order_item:
> id | id_order | price |
> 1 | 1 | 21.23 |
> 2 | 1 | 21.32 |
> 3 | 2 | 21.23 |
> 4 | 2 | 21.32 |
> 5 | 3 | 21.23 |
> 6 | 3 | 21.32 |
> So, Abigail have 2 orders, steve 1, every order have 2 items.
>
> If i would to retrieve the number of orders, the number of total items
> bought (total sum of every order) for every customer, the query shuold
> be like that:
> ---
> SELECT
> customer.name,
> COUNT(orders.id),
> (
> SELECT
> COUNT(order_item.id)
> FROM
> orders
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> WHERE orders.id_customer = customer.id
> GROUP BY orders.id_customer
> ) AS total_items
> FROM
> customer
> INNER JOIN orders ON (orders.id_customer = customer.id)
> GROUP BY customer.id, customer.name
> ---
>
> Right?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Windows Live™: Keep your life in sync. Check it out.

On Tue, May 5, 2009 at 9:10 AM, DaNieL..! <daniele.pignedoli@gmail.com> wrote:
> Hi guyst.. thanks for the replies, really, them make me suppose that
> all what i've learned of sql from mysql can be wrong..

Replace "all" with "much" and you pretty much got the problem with
using MySQL for your first database experience. (Not that I didn't fell into
that trap, too)

DaNieL..! wrote:
> Hi guyst.. thanks for the replies, really, them make me suppose that
> all what i've learned of sql from mysql can be wrong..
>
> But still i have some trouble to understand the functionality of the
> orders example.
> My first goal is to retrieve every order, the customer name, and the
> total of the idems per order.. so (from my point of view) i *dont*
> need and *dont* ant to group by the costumer name, becose every
> costumer can have many orders...
> and, as i understand the query:
> ---
> 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, customer.name
> ---
> it *try* to group by the customer name, but *fail* becose the GROUP
> priority is at the order's code.. how much am i far from it?
> but aswell i cant understand why i must group for the costumer name,
> when i dont want and dont need it to be *unique*


I think you are arguing with yourself.  At some level, "group by
orders.id" and "group by orders.code, customer.name" is exactly the same
thing.

However, its only exact because you know the details of the database
layout.  PG wont make that assumption.  Look at your query, and this one:

temp table:
x   | name | value
-----------------
1   | Bob  | 22
1   | Joe  | 23

I'm not going to tell you what field X is.  Its not what you think.

select name, sum(value) from temp group by x;

I'm grouping by a field not in the result set.  field X does not have
any correlation to the name, so the query above makes no sense.

I think of "group by" as two separate operations.  First the query is
run to get the full table.  So for your query above:

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

we'd get like:

code | name   | price
--------------------
0001 | Abigail | 10
0001 | Abigail | 11
0001 | Abigail | 12
0002 | Steve   |  5
0002 | Steve   |  6
0002 | Steve   |  7

The only columns in the result set are code, name and price. Then we go
through the result set and sum things up based on the group by.

-Andy

On Tue, 2009-05-05 at 00:10 -0700, DaNieL..! wrote:
> But still i have some trouble to understand the functionality of the
> orders example.
> My first goal is to retrieve every order, the customer name, and the
> total of the idems per order.. so (from my point of view) i *dont*
> need and *dont* ant to group by the costumer name, becose every
> costumer can have many orders...
> and, as i understand the query:

If I understand you correctly: every customer can have many orders, and
every order can have many items, and you want the sum of all items per
order per customer.

So, you need to group by the combination of order and customer. Grouping
by the combination of those two is _not_ the same as grouping by order,
and then grouping by customer.

> ---
> 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, customer.name
> ---

In this query, you should "GROUP BY orders.code, customer.name".

> it *try* to group by the customer name, but *fail* becose the GROUP
> priority is at the order's code.. how much am i far from it?

What is "GROUP priority"? I don't think that has any meaning.

> but aswell i cant understand why i must group for the costumer name,
> when i dont want and dont need it to be *unique*

customer.name will _not_ necessarily be unique, if you "GROUP BY
orders.code, customer.name". The _combination_ of orders.code and
customer.name" will be unique.

Regards,
    Jeff Davis