how to sum up this thing?

hi guys.
in my select statement, i have this
CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid

the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.

The simplified query....

(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid

Here you can use join rather subquery, if you post the full query i may help you to tune..

|||
SELECT TOP 5 tr.year, CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid, tr.edemedpoints, tr.adjustedpoints, tr.expiredpoints

FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year

i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.