Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query - Mailing list pgsql-general

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?