Using avg() function in having clause

    

SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );

Table created.

SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,'14-Feb-1999', 123.12, '14-Feb-1999', '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,'14-Feb-1999', 50.98, '14-feb-1999', '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,'14-Feb-1999', 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,'14-Feb-1999', 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,'4-mar-1999', 10.95, '5-mar-1999', '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');

1 row created.

SQL>
SQL>
SQL> select to_char(register_date, 'YYYY/MM') "Month",
  2         avg(total_price) "Avg Sales by Month"
  3    from gift
  4   group by to_char(register_date, 'YYYY/MM')
  5  having avg(total_price) > 35;

Month   Avg Sales by Month
------- ------------------
1999/02              57.51

1 row selected.

SQL>
SQL>
SQL> drop table gift;

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 the HAVING Clause with aggregate function
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

Using avg() function in having clause : HAVING « Select Query « Oracle PL / SQL

Related examples in the same category