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