Re: how to use an aggregate function

ggunning_at_esatclear.ie (jmsmithe) wrote in message news:<de0fa271.0305170835.49ea138c_at_posting.google.com>...

[...]

>
> 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
> ;
>
> I can't figure out how to limit it to over $6?
>

add a HAVING clause, as in

SELECT Customer.Cname, AVG(Item.UnitPrice) AS Avg_Paid ...
ON OrderItem.ItemID = Item.ItemID
GROUP BY Customer.Cname
HAVING AVG(Item.UnitPrice) > 6
;

HTH
/Lennart

PS. Normally one declares all columns that belongs to the p.k. as not null. Perhaps your db handles that, but your ddl will not work if you try to move it to another db DS. Received on Sat May 17 2003 - 23:11:55 CDT