X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D39E1D1B4E1 for ; Sun, 28 Sep 2003 19:57:12 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 12087-02 for ; Sun, 28 Sep 2003 16:57:04 -0300 (ADT) Received: from anchor-post-37.mail.demon.net (anchor-post-37.mail.demon.net [194.217.242.87]) by svr1.postgresql.org (Postfix) with ESMTP id 963C1D1B52D for ; Sun, 28 Sep 2003 16:57:01 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1A3hfC-0006uC-0b; Sun, 28 Sep 2003 20:57:02 +0100 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.22) id 1A3hew-0004qe-8K; Sun, 28 Sep 2003 20:56:46 +0100 Subject: Re: SUM() & GROUP BY From: Oliver Elphick To: "Muhyiddin A.M Hayat" Cc: pgsql-sql@postgresql.org In-Reply-To: <001401c385ea$836f5960$29179fca@middinkcomp> References: <001401c385ea$836f5960$29179fca@middinkcomp> Content-Type: text/plain Organization: LFIX Limited Message-Id: <1064779015.1458.4.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sun, 28 Sep 2003 20:56:56 +0100 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/368 X-Sequence-Number: 15346 On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote: > > hotel=# SELECT > hotel-# "public".billing.id, > hotel-# "public".billing.guest_id, > hotel-# "public".billing.trx_date, > hotel-# "public".billing.trx_time, > hotel-# "public".billing.payment_method, > hotel-# "public".billing.tax, > hotel-# "public".billing.dep_id, > hotel-# "public".department."name", > hotel-# SUM("public".items.price) AS total, > hotel-# "public".billing.amount_paid > hotel-# FROM > hotel-# "public".billing_items > hotel-# INNER JOIN "public".billing ON > ("public".billing_items.billing_id = "public".billing.id) > hotel-# INNER JOIN "public".department ON ("public".billing.dep_id = > "public".department.id) > hotel-# INNER JOIN "public".items ON > ("public".billing_items.items_id = "public".items.id) > hotel-# GROUP BY "public".billing.id; > ERROR: Attribute billing.guest_id must be GROUPed or used in an > aggregate function > hotel=# > > What Worng ?? Any items in the select list need to be aggregated (e.g. SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose there are several billing.guest_id values for each billing.id; which value should be listed in the output? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Blessed is the man that endureth temptation; for when he is tried, he shall receive the crown of life, which the Lord hath promised to them that love him." James 1:12