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

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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