You win! I hadn't thought about putting the SUM in front of the whole thing. Now here's another part of the puzzle. My select currently includes creation of an empty field like this.

CAST(0 AS numeric(12,2)) as julPrior,

Which is then followed by

update TempRevenues set julPrior = (select amount from [RTA-Revenues] where category = TempRevenues.CATEGORY and MONTH(receiveddate)=7 and fisyear = TempRevenues.FISYEAR - 1 )

to populate it. Can you think of a way to do this in 1 step instead of 2? I suppose the CASE could be worked into that second line as well but what I'd really like to do is eliminate that second line.



>Realy?
>

>SUM(CASE WHEN month([receiveddate])=7 THEN [AMOUNT] ELSE 0.00 END) as Jul
>

>
>
>>CASE doesn't work. It returns me to the original error demanding receiveddate and amount be included in the group by clause.
>>
>>>CASE would be more efficient than subqueries
>>>
>>>>OK. Not sure if this is the best solution but it seems to work.

>>>>select [fisyear], [category], 
>>>>(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=7 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as jul,
>>>>(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=8 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as aug,
>>>>(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=9 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as sep
>>>>from [rta-revenues] as crsA
>>>>group by [fisyear],[category]
>>>>order by FISYEAR, CATEGORY
>>>>

>>>>Note it does not use the CASE statement we had been playing with. What I don't understand is why the former did not work and why it insisted on having receiveddate in the group by clause, since it was not actually in the select list. It was just included in a function within the select list. Oh well, thanks for your help. As always, you made me think.