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

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