Skip the CURRENT ROW and apply analytical function for all remaining rows in SQL Server 2012 [closed]

Question

Skip the CURRENT ROW and apply analytical function for all remaining rows in PARTITION BY clause in SQL Server 2012

Example: I have 12 rows of data in a table and I want to calculate and append the AVERAGE of entire column by skipping the CURRENT ROW value.

COL1    COL2    AVG_SKIP_CUR_ROW 
a          3    7.71
a          6    7.28
a          6    7.28
a          8    7
a          12   6.42
a          5    7.43
a          8    7
a          9    6.86

Answer 1

Following up on my comment, if you wish to apply the function to everything after the current row, use ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING or for everything before the current row, ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

Here's a simple example of (a possible, might not be ideal) way to combine these to exclude the current row. In this case, a simple SUM aggregate (you could do this in a single select, I split it into a sub query for clarity):

CREATE TABLE #Test(Val INT)

INSERT INTO #Test VALUES(1),(2),(3),(4),(5)

SELECT Val, SumPreceding+SumFollowing SumExcludingCurrent FROM (
    SELECT Val
    ,ISNULL(SUM(Val) OVER (ORDER BY VAL ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) SumPreceding
    ,ISNULL(SUM(Val) OVER (ORDER BY VAL ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 0) SumFollowing
    FROM #Test
    ) A
ORDER BY Val

Val         SumExcludingCurrent
----------- -------------------
1           14
2           13
3           12
4           11
5           10

For your update, since you're calculating the average of the col excluding the current value, this simplifies to:

CREATE TABLE #Test(Col1 CHAR, Col2 INT)

INSERT INTO #Test VALUES('a',3),('a',6),('a',6),('a',8),('a',12),('a',5),('a',8),('a',9)

SELECT Col1
       ,Col2
       ,(SUM(Col2) OVER () - Col2) / CAST(COUNT(*) OVER() - 1 AS NUMERIC(4,2)) Avg_Skip_Cur_Row
    FROM #Test


Col1 Col2        Avg_Skip_Cur_Row
---- ----------- ---------------------------------------
a    3           7.714285
a    6           7.285714
a    6           7.285714
a    8           7.000000
a    12          6.428571
a    5           7.428571
a    8           7.000000
a    9           6.857142

And because I was having fun with this, here's another version. Since the test data doesn't have a key, I've added a row number.

SELECT
    Col1
    ,Col2
    ,(ISNULL(SUM(Col2) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) +
      ISNULL(SUM(Col2) OVER (ORDER BY rn ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0)) /
      CAST(COUNT(*) OVER() - 1 AS NUMERIC(4,2)) Avg_Skip_Cur_Row
FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY Col2) rn FROM #Test) A