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@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
