Using the HAVING Clause with aggregate function : HAVING « Select Query « Oracle PL / SQL






Using the HAVING Clause with aggregate function

    
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) VALUES (2,6,63,1);

1 row created.

SQL>
SQL>
SQL> SELECT StudentID, AVG(Mark) AS AverageMark
  2  FROM SAT
  3  GROUP BY StudentID
  4  HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

no rows selected

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

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Example using the MAX function with having clause
2.Using the HAVING Clause
3.Any conditions based on the outcome of a group function must be in the HAVING clause
4.Using the SUM function in HAVING Clause
5.Using HAVING with an Analytical Function
6.Sub query inside having clause
7.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
8.Use sum in having clause
9.Using the HAVING Clause and where clause
10.Using avg() function in having clause
11.Using and, or operator in having clause
12.Born after '1960-01-01', group by department number with count(*) >= 4;
13.Using the same condition in having and where