Remove duplicates from SQL Window function-CodePudding

I'm trying to sum values inside a window function but I can't figure out have to prevent summing duplicates. Below is a snippet of the results I have right now. For the last column I want to calculate REG_MOVEMENT summed across unique STORE_ID's and then divide it by the number of unique stores. This column should be 5603.5 ((9359 1848)/2) since there are 3 rows with the same STORE_ID and one different.

KEY_ID	PRODUCT_ID	STORE_ID	REG_MOVEMENT	(No column name)
154	5214266	28002	9359	7481.25
155	5214266	28002	9359	7481.25
156	5214266	28002	9359	7481.25
173	5214266	28005	1848	7481.25

My current code is

SELECT
     KEY_ID,
     PRODUCT_ID,
     STORE_ID,
     REG_MOVEMENT,
     SUM(REG_MOVEMENT) OVER(PARTITION BY PRODUCT_ID) / CONUT(STORE_ID) OVER(PARTITION BY PRODUCT_ID)

CodePudding user response：

You need a distinct count in the denominator, but SQL Server does not allow this in a single count window function call. As a workaround, we can use DENSE_RANK:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY PRODUCT_ID ORDER BY STORE_ID) dr
    FROM yourTable
)

SELECT
    KEY_ID,
    PRODUCT_ID,
    STORE_ID,
    REG_MOVEMENT,
    SUM(REG_MOVEMENT) OVER (PARTITION BY PRODUCT_ID) /
        MAX(dr) OVER (PARTITION BY PRODUCT_ID) AS new_col
FROM cte
ORDER BY PRODUCT_ID, STORE_ID;

CodePudding user response：

One way with a subquery to de-duplicate (store_id, reg_movement) rows:

select 
  KEY_ID,   PRODUCT_ID, STORE_ID,   REG_MOVEMENT,
  (select avg(reg_movement) 
   from (select distinct store_id, reg_movement 
         from Tbl) Unq
  ) As NewCol
from Tbl

(Tbl is yourtable)

CodePudding user response：

select AVG(reg_movement) from (
select distinct store_id, cast(reg_movement as float) as reg_movement
         from Table1) a