There are ways to take sums and then to take averages of those sums, but they usually involve different groupings. For example I could take the sum of measurements for a day to produce a daily total, which I could then average over a month to give me an average daily total for the month. Is that what you want to do?
I'm missing something in your question, because looking at your data sample, I can't seem to find the desired results.
declare @sample table (BEGIN_DATE datetime, MARKET_CODE varchar(5), RATE_AMOUNT numeric(5,2))
insert into @sample
SELECT '12-MAR-2009', 'ADS', 187.5
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PSBY', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'CONT', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'ADS', 199
UNION ALL SELECT '12-MAR-2009', 'CONT', 199
UNION ALL SELECT '12-MAR-2009', 'ADS', 139.3
UNION ALL SELECT '12-MAR-2009', 'ADS', 141
UNION ALL SELECT '12-MAR-2009', 'ADS', 141
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'ADS', 167.14
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'NLRA', 265
UNION ALL SELECT '12-MAR-2009', 'ADS', 172.5
UNION ALL SELECT '12-MAR-2009', 'PKGS', 199.99
UNION ALL SELECT '12-MAR-2009', 'HSE', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'BAR', 330
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 250
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'PRO', 249
select begin_date,market_code,sum(rate_amount) as sumRate,cast(avg(rate_amount) as numeric(5,2)) as avgRate
from @sample
group by begin_date,market_code
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills