Re: how to use an aggregate function

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
======================================================