>Did you try my suggestion?

I think I did without realizing it <g> and the result was as shown in my other reply. Thanks, I think your approach was correct, if what I am showing there is what you were suggesting.


>
>In other words, just put select *, .. as balance from (your whole select here) InnerSelect
>
>>Thanks much, Sergey. Just couldn't figure out how to get the three values resolved before using them to get the fourth.
>>
>>I'm getting an "incorrect syntax" error near ')' on the last line ??? Have a feeling the paren is not the problem but just that everything inside won't resolve.
>>
>>The cross join is interesting. I get that it will yield a Cartesian product so all three fields end up in dbt , no?
>>
>>Does the primary select need aliasing? I've tried various permutations, but debtis and creditedamt don't seem to work with dbt, ad or sm
>>
>>
>>
>>>Hi Charles,
>>>
>>>You can use derived table to add a calculated column. Try
>>>

>>>SELECT *, credits-debits-creditedamt AS balance
>>>	FROM (
>>>		SELECT SUM(ac.yamount) AS credits
>>>			FROM account_credits ac
>>>		CROSS JOIN (
>>>				SELECT sum(ad.yamount) AS debits,
>>>					sum(ad.ycreditedamt) AS creditedamt
>>>				FROM account_debits ad
>>>				WHERE ad.cinmates_id=@cinmates_id
>>>			) dbt
>>>		WHERE ac.cinmates_id = @cinmates_id)
>>>	) sm
>>>

>>>I feel that it can be improved but nothing else comes to mind at the moment.
>>>
>>>>

	SELECT
>>>>		credits = (SELECT SUM(ac.yamount)
>>>>			FROM account_credits ac
>>>>			WHERE ac.cinmates_id = @cinmates_id) ,
>>>>		debits = (SELECT sum(ad.yamount)
>>>>			from account_debits ad
>>>>			WHERE ad.cinmates_id=@cinmates_id) ,
>>>>		creditedamt = (SELECT sum(ad.ycreditedamt)
>>>>			from account_debits ad
>>>>			WHERE ad.cinmates_id=@cinmates_id )
>>>>

>>>>
>>>>In the above stored proc I would like to add a 4th colum which is balance=credits-debits-creditedamt
>>>>
>>>>Would also appreciate any insights as to other ways to design this select.
>>>>