Query From Hell!!!!

Hello Everyone
 I have been having a hardtime with this query for awhile so I though I would ask for some help. I have to write a query that 1st pulls a store name from the store table the field is called store_nm. Then from a second table I need to avg(on_hand_qty). The two tables have a column called store_id in common. The query is to return the avg inventory for each store as long as it is greater then avg for all of the stores plus 10. I know that I have some group by issues that need to be resolved but here is what I tried. It does return the stores that have a avg inventory that is higher than the average plus ten but does not return the average for each store.

SELECT s.store_nm
FROM store s, inventory i
WHERE s.store_id = i.store_id
AND i.on_hand_qty > (SELECT AVG(on_hand_qty)+10 FROM inventory)

Thanks for the help! Liam

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Mar 18 2000 - 00:00:00 CST