In response to DaNieL <daniele.pignedoli@xxxxxxxxx>: > 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? Because the database serer has no way to know that it can do that. You're grouping by order.id, because you _know_ that doing so will capture what you want. PostgreSQL has no way of knowing this, what it _does_ know is that there are certain possible table layouts in which doing without grouping by both statements could lead to incorrect results. Someone with a more expensive education than me may jump in with more details, but I'm pretty sure this behaviour is mandated by the SQL standard. I mean, PostgreSQL _could_ behave the same way as MySQL, but he designers chose this way because it's more reliable. > Sorry, i know that this maybe is a basically problem, but i come from > mysql.. and in mysql that query works... That's a shame, really ... that MySQL allows you to execute a query that could produce indeterminate results. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
