SUM Question

[dba112233]

Let's say you have the following

Acct# Amount
1122 300
1122 300
1122 500

How do I write a SUM on Amount and tell it not to add both 300, just one of them with the 500? So basically I'm treating any amounts with the same value as one unique amount then adding it to other unique amounts. The problem is whoever designed this 3rd party database were stupid. So I am left with this problem of ensure I don't get 300 + 300 + 500, just 300 + 500

I've already tried DISTINCT on Amount but that doesn't work.

 

[gmmastros]

What is the data type for the amount column?

Select Table_Name, 
       Column_Name, 
       Data_Type 
from   Information_Schema.Columns 
Where  Table_Name = 'YourTableName' 
       And Column_Name = 'YourColumnName'

By the way, this works, so it must be something else.

Declare @Temp Table(Acct Integer, Amount Real)

Insert Into @Temp Values(1122,        300)
Insert Into @Temp Values(1122,        300)
Insert Into @Temp Values(1122,        500)

Select 	Acct,
		Sum(Amount) as AmountSum
From	(
		Select Distinct 
				Acct, 
				Amount 
		From 	@Temp
		) A
Group By Acct

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dba112233]

How would I apply that to this then:

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

 

[gmmastros]

Before you apply it to your query, try running it seperately.

SELECT  RMSFILENUM, 
        SUM(rmstranamt) AS rmstranamt10
From	(
		SELECT  Distinct RMSFILENUM, rmstranamt
		FROM 	RFINANL
		WHERE 	RMSTRANCDE = '10'
		) A
GROUP BY RMSFILENUM

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lekar]

You can specify the keyword Distinct in the SUM function.

Select Sum(Distinct Amount)
From TableName