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);

Group Filter doesn't work as expected

Employee (Group H1) AVG( of all Ratings)
Job # (Group H2) Rating
Rating is a "Calculated" field in the Dataset.
I put a filter on Job# (Group H2) to only include Ratings > 25%
Results:
Job#'s are filtered properly and every job < 25% does NOT show. HOWEVER,
the AVG (of all Ratings) ignores the Group 2 filter and includes every job
even those with a rating < 25%.
How can I get the "Avg Rating" to also only average those ratings according
to the group 2 filter I setup?
mythreadsHi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: True
HTH!
Kind regards - Fred|||Hi,
Try setting up th filter this way. Write the expression return a true/false
value:
For expression use this (or similar): Job# > 25%
For Operator use: =For Value use: =True (*** CORRECTED FROM LAST POST***)
HTH!
Kind regards - Fred|||Thanks Fred for responding.
Your suggestion produced the same results. After further digging we solved
this original problem by putting the Filter on the "Dataset" and not the
report. I didn't know you could do this.
When I put the filter on "Group 2" header, "Group 2" detail was correct, but
"Group 1" header ignored the filtering in "Group 2" and used the raw Dataset
information.
When we put the filter on the dataset, it applied to both Group 1 and Group
2.
Thanks again.
"Fred Block" wrote:
> Hi,
> Try setting up th filter this way. Write the expression return a true/false
> value:
> For expression use this (or similar): Job# > 25%
> For Operator use: => For Value use: =True (*** CORRECTED FROM LAST POST***)
> HTH!
> Kind regards - Fred
>
>