SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T1
(
  keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
  (2, 'A'),(3, 'A'),
  (5, 'B'),(7, 'B'),(11, 'B'),
  (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');
9 rows affected

SELECT col1
, keycol
, SUM(keycol)  OVER (PARTITION BY col1) AS mysum
, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg 
FROM T1
order by col1;


col1
keycol
mysum
RowAvg


A
2
5
2

A
3
5
2

B
5
23
7

B
7
23
7

B
11
23
7

C
13
72
18

C
17
72
18

C
19
72
18

C
23
72
18


SELECT keycol
, col1
, SUM(keycol)  OVER (ORDER BY keycol) AS mysum
, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
order by col1;


keycol
col1
mysum
RowAvg


2
A
2
2

3
A
5
2

5
B
10
3

7
B
17
4

11
B
28
5

13
C
41
6

17
C
58
8

19
C
77
9

23
C
100
11


SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 ORDER BY keycol) AS mysum
, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
order by col1;


keycol
col1
mysum
RowAvg


2
A
2
2

3
A
5
2

5
B
5
5

7
B
12
6

11
B
23
7

13
C
13
13

17
C
30
15

19
C
49
16

23
C
72
18


SELECT keycol
, col1
, AVG (keycol) OVER (PARTITION BY col1 
                     ORDER BY keycol
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sliding_average
FROM T1
order by col1;


keycol
col1
sliding_average


2
A
2

3
A
2

5
B
6

7
B
7

11
B
9

13
C
15

17
C
16

19
C
19

23
C
21