Re: how to use an aggregate function

On Saturday 17 May 2003 17:18 in
<de0fa271(dot)0305170818(dot)5972e18e(at)posting(dot)google(dot)com>, jmsmithe
(ggunning(at)esatclear(dot)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(at)spamtrap(dot)ntlworld(dot)com (David W Noon)
Remove spam trap to reply via e-mail.
RLU#314465
======================================================