Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Analytical Functions

 

Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)

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> 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> SELECT StudentID, Mark, AVG(Mark) OVER   2  (PARTITION BY StudentID   3   ORDER BY StudentID, Mark) Running_Avg_by_Student   4  FROM SAT   5  ORDER BY StudentID, Mark;  STUDENTID       MARK RUNNING_AVG_BY_STUDENT ---------- ---------- ----------------------          1         55                     55          1         73                     64          2         39                     39          2         44                   41.5          2         63             48.6666667 5 rows selected. SQL> SQL> drop table SAT; Table dropped.