Monday, March 26, 2012

grouping by the avg

I'm trying to resolve the problem :
List the customer number, customer name, rep number, and credit balance for all customers whose credit balance is greater than the average credit balance for the customers who have the same rep. Sort by rep number. Use of 'exists' is required.
See the table behind . My query is

select customer_num, customer_name, rep_num, balance
from customer a
where exists
(select * from customer b
where balance >
(select AVG(balance) from customer c
where a.rep_num=b.rep_num)
and a.rep_num=b.rep_num)
order by rep_num;

but it lists all the customers from the table. Thank you.

CUS CUSTOMER_NAME STREET CITY ST ZIP BALANCE CREDIT_LIMIT RE
-- ------- ----- ---- -- -- ---- ---- --
148 Al's Appliance and S 2837 Fillmore FL 33336 6550 7,500 20
port Greenway

282 Brookings Direct 3827 Grove FL 33321 431.5 10,000 35
Devon

356 Ferguson's 382 Northfield FL 33146 5785 7,500 65
Wildwood

408 The Everything Shop 1828 Crystal FL 33503 5285.25 5,000 35
Raven

462 Bargains Galore 3829 Grove FL 33321 3412 10,000 65
Central

524 Kline's 838 Fillmore FL 33336 12762 15,000 20
Ridgeland

608 Johnson's Department 372 Sheldon FL 33553 2106 10,000 65
Store Oxford

687 Lee's Sport and Appl 282 Altonville FL 32543 2851 5,000 35
iance Evergreen

725 Deerfield's Four Sea 282 Sheldon FL 33553 248 7,500 35
sons Columbia

842 All Season 28 Lakeview Grove FL 33321 8221 7,500 20select customer_num, customer_name, rep_num, balance
from customer a
where a.balance > (select avg(balance) from customer where rep_num = a.rep_num);

No comments:

Post a Comment