Write a special query in SQL Server 2000

Question

My database is SQL Server 2000 and can't use sum...over by...

This is my code

select 
    cu_account,na_debtamount 
from 
    acc.acc_voucherheader vh 
inner join 
    acc.acc_voucherdetail vd on vh.si_voucherheader = vd.si_voucherheader 
inner join 
    acc.acc_codebook cb on cb.si_account = vd.si_account
where 
    cb.si_account in (1700052, 1700053)   

and the result is like this :

cu_account  na_debtamount   
--------------------------    
60060322       400  
60060322       400  
60060302     25897  
60060310     60917  
60060310     61195  
60060310     64404  
60060310     64404  
60060310     71225  
60060310     84839  
60060306    100000  
60060306    100000  
60060306    100000  
60060306    100000  
60060306    100000  
60060306    100000  
60060306    100000  
60060306    100000  

I want to change my code that show result the sum of na_debtamount for every cu_account like this :

cu_account     na_debtamount
-------------------------------    
60060322       sum (all the columns that have same cu_account = 60060322)
60060306       sum (all the columns that have same cu_account = 60060306)
and....

Thanks

Answer 1

If I understood you correctly, you can simply group by the account and use a regular sum.

select 
    cu_account,sum(na_debtamount) AS na_debtamount
from 
    acc.acc_voucherheader vh 
    inner join acc.acc_voucherdetail vd on 
        vh.si_voucherheader= vd.si_voucherheader 
    inner join acc.acc_codebook cb on 
        cb.si_account =vd.si_account
where 
    cb.si_account in (1700052,1700053)   
group by
    cu_account