Try somthing like this...

SELECT TENANCYID=Max(TENANCYID),

SUPPLIERID,

EMPLOYEEID,

InvAmount,

openingBalance = (SELECT Sum(openingBalance) FROM mytablename WHERE itemtype=12 GROUP BY itemtype) ,

payment= (SELECT sum(openingbalance) FROM mytablename WHERE itemtype<>12 GROUP BY itemtype)

FROM mytablename

GROUP BY...

Thank you!

Thanks for advice.  I'm new at this and need all the help I can get.

I really appreciate your help!

Here is a stripped down version (and below that is the entire stored procedure)

SELECT L.[CFT Location]

 , DateMonth=CAST(LEFT(DATENAME(m,DS.DenialDate),3)AS varchar) + '-' + CAST((DATEPART(yy,DS.DenialDate)) AS varchar)

 , DS.DenialDate

 , DR.RemitCode

 ,...

The second statement works but I need to had a HAVING (or WHERE) clause so that it can relate back to the main statement.

This works alone:

SELECT SUM(A.DeniedCharge) 

FROM ( SELECT DISTINCT Account, DenialDate,...

Thanks Remi.  My challenge is that I need to get the DenialCharge only once for each Account,DenialDate,Location,DenialCode group (so just the values $296.51, $1,400.00, and $200.00) and then sum them...

Thanks.  Almost there.  In the main statement I group by DenialDate, Location and DenialCode.  How do I alter the nested SUM statement so that it will group on the same fields?  Here...

I'm new at this and wasn't aware of print (string) but doing this I found my problem! 

 It was really stooopid.  I had limited the @strSQL variable to 800 so it...