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.
>>