Using Analytic Functions: AVG(Mark) OVER : OVER « Analytical Functions « Oracle PL/SQL Tutorial

SQL>
SQL> CREATE TABLE SAT (
  2     StudentID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     IfPassed   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created.

SQL>
SQL>
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,'Satisfactory');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,'Good result');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,'Hard');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,'Simple');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1);

1 row created.

SQL>
SQL>
SQL> SELECT StudentID, Mark, AVG(Mark) OVER
  2  () Average_Mark
  3  FROM SAT
  4  ORDER BY StudentID, Mark;

 STUDENTID       MARK AVERAGE_MARK
---------- ---------- ------------
         1         55         54.8
         1         73         54.8
         2         39         54.8
         2         44         54.8
         2         63         54.8

5 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table SAT;

Table dropped.

16.20.OVER
	16.20.1.	AVG(salary) OVER
	16.20.2.	ROUND(AVG(salary) OVER())
	16.20.3.	Use partitioning in the OVER clause of the aggregate-analytical function
	16.20.4.	Ratio-to-Report
	16.20.5.	Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
	16.20.6.	Using Analytic Functions: AVG(Mark) OVER
	16.20.7.	Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
	16.20.8.	Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	16.20.9.	sum over (nothing)
	16.20.10.	sum over partition by, order by
	16.20.11.	sum salary over PARTITION BY