X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id DE26B924C1F for ; Sat, 17 May 2003 14:00:12 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 69347-06 for ; Sat, 17 May 2003 14:00:05 -0400 (EDT) Received: from news.hub.org (unknown [64.117.225.234]) by developer.postgresql.org (Postfix) with ESMTP id C03FB924AFA for ; Sat, 17 May 2003 14:00:05 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 3E5B0252354; Sat, 17 May 2003 13:58:41 -0400 (EDT) Message-ID: <287ip-3hb.ln1@my-pc.ntlworld.com> From: David W Noon Subject: Re: how to use an aggregate function X-Newsgroups: comp.databases.postgresql.sql References: Lines: 41 User-Agent: KNode/0.7.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit Date: Sat, 17 May 2003 18:14:09 +0100 X-Complaints-To: abuse@ntlworld.com Organization: ntl Cablemodem News Service To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/432 X-Sequence-Number: 13597 On Saturday 17 May 2003 17:18 in , jmsmithe (ggunning@esatclear.ie) wrote: [snip] > How would I > List the name of customers who have paid an average of more then $6. > Produce a listing Cname, Avg_Paid In your Item table, UnitPrice is an INT. Please choose a better data type for monetary values, as an average is problematic in an integer domain: the average is usually not an element of that domain. Something like DECIMAL(6,2) could be good. > All I can think of is this. > > SELECT Customer.Cname, AVG(Item.UnitPrice) AS Avg_Paid > FROM Customer > JOIN AOrder > ON Customer.CustomerID = AOrder.CustomerID > JOIN OrderItem > ON AOrder.AOrderID = OrderItem.AOrderID > JOIN Item > ON OrderItem.ItemID = Item.ItemID > GROUP BY Customer.Cname HAVING Avg_paid > 6.00 > ; Try this after changing the data type on Item.UnitPrice. -- Regards, Dave ====================================================== dwnoon@spamtrap.ntlworld.com (David W Noon) Remove spam trap to reply via e-mail. RLU#314465 ======================================================