Re: Query From Hell!!!!

From: Zlatko Baumberger <zb_at_orcanet.ch>
Date: 2000/03/18
Message-ID: <38D386DB.9297E304@orcanet.ch>#1/1

Hi Liam,

Try this:
##########

select s.store_id, avg(i.on_hand_qty)
  from store s, inventory i
 where i.store_id = s.store_id
 group by s.store_id
 having avg(i.on_hand_qty) > (select avg(on_hand_qty)+10 from inventory) ;

##########
with following tables :
##########

create table store (
  store_id integer
);
create table inventory (
  store_id integer,
  on_hand_qty integer
);

##########
and values:
##########

insert into store values ( 1 );
insert into store values ( 2 );
insert into store values ( 3 );
insert into store values ( 4 );

insert into inventory values ( 1, 100 );
insert into inventory values ( 1, 110 );
insert into inventory values ( 1, 130 );

insert into inventory values ( 1, 140 ); insert into inventory values ( 1, 150 );

insert into inventory values ( 2, 100 );
insert into inventory values ( 2, 120 );
insert into inventory values ( 2, 130 );
insert into inventory values ( 2, 140 );
insert into inventory values ( 2, 190 );

insert into inventory values ( 3, 100 );
insert into inventory values ( 3, 150 );
insert into inventory values ( 3, 100 );

insert into inventory values ( 3, 100 ); insert into inventory values ( 3, 100 );

insert into inventory values ( 4, 100 );
insert into inventory values ( 4, 150 );
insert into inventory values ( 4, 150 );
insert into inventory values ( 4, 150 );
insert into inventory values ( 4, 150 );

#########
The average on_hand_qty is : 128

the result of the query is

 STORE_ID AVG(I.ON_HAND_QTY)

--------- ------------------
        4                140

########

I hope this helps you.

regards

Zlatko Baumberger

OrcaNet GmbH
Anglikerstrasse 96
5612 Villmergen
Switzerland

liam_2e_at_my-deja.com wrote:
>
> 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