eliminating duplicate rows

[iwm]

The fields I am working with are ACCOUNT#, CODE, AMOUNT. I need to sum the AMOUNT field when the ACCOUNT#/CODE are the same, listing the ACCOUNT# and CODE only once.

I am currently using the following code:

select CodeMaster.code,
BCM.Account,
Fund.fundID as procureID,
'Cntr&ObjCdFcAmt'=Convert(nvarchar(20),
((select Sum (amount) from Fund FC
join CodeMaster COM on COM.coaID = FC.debitCOAID
join BudCentMaster on FC.bcID = BudCentMaster.bcID
where FC.fundID = Fund.fundID
and BudCentMaster.bcID = BCM.bcID
and CodeMaster.coaID = COM.coaID)))

from Fund
inner join BudCentMaster BCM
on Fund.bcID = BCM.bcID
inner join CodeMaster
on CodeMaster.coaID = Fund.debitCOAID
inner join ProcureMaster
on ProcureMaster.procureID = Fund.fundID
and Fund.bcID = BCM.bcID

inner join ProcureMaster
on ProcureMaster.procureID = Fund.fundID
and Fund.bcID = BCM.bcID

QUERY RESULTS:

Account Code Amount
123456 ABC $206.00 (record #1)
246810 XYZ $100.00 (record #2)
123456 ABC $206.00 (record #3)
123456 ABC $206.00 (record #4)
123456 ABC $206.00 (record #5)
246810 XYZ $100.00 (record #6)
567829 QRS $100.00 (record #7)

As you can see their are duplicate ACCOUNT#/CODES. How do I change the code to eliminate the duplicate ACCOUNT#/CODE fields?

My desired QUERY RESULT is as follows:


ACCOUNT# CODE AMOUNT
123456 ABC $206.00 (record #1, #3, #4, #5)
246810 XYZ $100.00 (record #6, #2)
567829 QRS $100.00 (record #7)



Your assistance in this matter is greatly appreciated.

 

[jbenson001]

Your question is confusing. Do you want to SUM where the account# and code columns are the same, or do you want to remove dups and only have one row where they are the same?

Jim

 

[AtomicWedgie]

select distinct code, account, amount
from (

select CodeMaster.code,
BCM.Account,
Fund.fundID as procureID,
'Cntr&ObjCdFcAmt'=Convert(nvarchar(20),
((select Sum (amount) from Fund FC
join CodeMaster COM on COM.coaID = FC.debitCOAID
join BudCentMaster on FC.bcID = BudCentMaster.bcID
where FC.fundID = Fund.fundID
and BudCentMaster.bcID = BCM.bcID
and CodeMaster.coaID = COM.coaID)))

from Fund
inner join BudCentMaster BCM
on Fund.bcID = BCM.bcID
inner join CodeMaster
on CodeMaster.coaID = Fund.debitCOAID
inner join ProcureMaster
on ProcureMaster.procureID = Fund.fundID
and Fund.bcID = BCM.bcID

inner join ProcureMaster
on ProcureMaster.procureID = Fund.fundID
and Fund.bcID = BCM.bcID)