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