Re: how to use an aggregate function
Date: 17 May 2003 21:11:55 -0700
Message-ID: <6dae7e65.0305172011.62b8ce0b_at_posting.google.com>
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 Sun May 18 2003 - 06:11:55 CEST