I have to replace repeated values based on a specific condition, the replacement value depends on the repeated value and changes between rows

Question

Hope you are doing good! I am having some trouble figuring this one out and couldn´t find any question that helps me with it.

The DB has multiple rows with repeated values,the sale price of real state propertys. Those rows have the total price of different propertys bought by the same person, thus the total amount from the sale of different propertys repeated on each row and not the individual price of each one.

Table image

This gives me problems when analyzing the data, so I want to replace that total amount with the division of that SalePrice by the number of repeated rows, so the sum returns the total amount of the sale.

My current code goes like this:

RowNumberCTE as (
select *,
ROW_NUMBER() OVER (PARTITION BY SalePrice, LegalReference, OwnerName
ORDER BY SalePrice) row_num
from DataCleaning..NashvilleHousing
)
Select *
from RowNumberCTE
ORDER BY SalePrice desc

And then I was thinking about a CASE Statement to replace the values as I described above until the row number of the current row is higher than the row number of the next row. (will only happen when the SalePrice changes meaning that it is not the same sale).

This approach needs a lot more, but it totally exceeds my sql knowledge.

score 0 · Answer 1 · answered Apr 19 '23 at 15:15

...so I want to replace that total amount with the division of that SalePrice by the number of repeated rows, so the sum returns the total amount of the sale...

Looks like You need count(), not row_number():

select UniqueId, SalePrice, LegalReference, OwnerName,
       SalePrice / count(1) over (partition by LegalReference, OwnerName, SalePrice) 
         as DividedPrice
from NashvilleHousing

dbfiddle demo (in Oracle, but it is standard SQL)

I have to replace repeated values based on a specific condition, the replacement value depends on the repeated value and changes between rows

1 Answers1