Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: 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 20

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select customer_num, customer_name, rep_num, balance
    from customer a
    where a.balance > (select avg(balance) from customer where rep_num = a.rep_num);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •