Re: Query From Hell!!!!

In article <8auibq$c26$1_at_nnrp1.deja.com>, liam_2e_at_my-deja.com writes
>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)
>

How about:

SELECT s.store_nm, i.on_hand_qty
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)

Andy

-- 
Andy Hardy. PGP key available on request
===============================================================