Search code examples
sqlteradatatera

TERADATA dividing value by number of duplicate rows

I have a small question for yall experts out there.

I have a table that contains a column with duplicate rows called Agreement_ID and the financial event amount is populated based on agreement ID as an external key, so what ends up happening is that i get the financial event amount value repeated twice ( in this case 94.7800 x2). Instead id like to divide the financial event amount based on the number of duplicate agreement ID. So in this case id like to have 47.3900 for each duplicate with the goal of having 94.7800 as the total sum of the financial event amount.

So basically to divide the financial event amount by the number of duplicate Agreement_id's.

I was thinking something along the lines of:

SEL AGREEMENT_ID, 
PARTY_ID,
FINANCIAL_EVENT_AMOUNT / COUNT(*)
FROM DB.TABLENAME
GROUP BY AGREEMENT_ID, PARTY_ID, FINANCIAL_EVENT_AMOUNT

But this does not seem correct obviously

enter image description here

Solution

  • You need to use a window function to get the number of dupes per agreement_id.

    The simplest way to approach this, at least in my opinion, is something like this:

    select
agreement_id,
party_id,
sum(amt) / min (dupes)
from (
select 
agreement_id,
party_id,
--get the # of dupes for each agreement_id
cast (count (*) over (partition by agreement_id) as decimal(10,2)) as dupes,
amt
from
<your table>
) t

group by
1,2