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

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