'Remove duplicates from SQL Window function

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)
sqlsql-servertsqlwindow-functions


Solution 1:[1]

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;

Solution 2:[2]

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)

Solution 3:[3]

SELECT AVG(reg_movement)
FROM (
    SELECT DISTINCT store_id,
        CAST(reg_movement AS FLOAT) AS reg_movement
    FROM Table1
) a

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 tinazmu
Solution 3 D M

Related Questions