From: David W Noon <dwnoon@spamtrap.ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: how to use an aggregate function
Date: Sat, 17 May 2003 18:14:09 +0100
Message-ID: <287ip-3hb.ln1@my-pc.ntlworld.com> (raw)
References: <de0fa271.0305170818.5972e18e@posting.google.com>
On Saturday 17 May 2003 17:18 in
<de0fa271.0305170818.5972e18e@posting.google.com>, 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
======================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dwnoon@spamtrap.ntlworld.com
Subject: Re: how to use an aggregate function
In-Reply-To: <287ip-3hb.ln1@my-pc.ntlworld.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox