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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
