Using and, or operator in having clause

    
SQL>
SQL> CREATE TABLE server_usage (
  2    pro_id                   NUMBER(4),
  3    emp_id                  NUMBER,
  4    time_log_date                DATE,
  5    hours_logged                 NUMBER(8,2),
  6    dollars_charged              NUMBER(8,2),
  7    CONSTRAINT server_usage_pk  PRIMARY KEY (pro_id, emp_id, time_log_date)
  8  );

Table created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1001,101,to_date('4-Apr-2004','dd-mon-yyyy'),1123,222);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1002,102,to_date('4-Apr-2005','dd-mon-yyyy'),1124,223);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1003,103,to_date('4-Apr-2006','dd-mon-yyyy'),1125,224);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1004,104,to_date('4-Apr-2007','dd-mon-yyyy'),1126,225);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1005,105,to_date('4-Apr-2008','dd-mon-yyyy'),1127,226);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1001,106,to_date('4-Apr-2009','dd-mon-yyyy'),1128,227);

1 row created.

SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
  2                    VALUES (1002,107,to_date('4-Apr-2010','dd-mon-yyyy'),1129,228);

1 row created.

SQL>
SQL> SET ECHO ON
SQL> SELECT emp_id, pro_id
  2  FROM server_usage
  3  GROUP BY emp_id, pro_id
  4  HAVING (pro_id = 1001 OR pro_id=1002)
  5     AND SUM(hours_logged) > 20;
       101       1001
       102       1002
       106       1001
       107       1002

4 rows selected.

SQL>
SQL> drop table server_usage;

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 avg() function 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 and, or operator in having clause : HAVING « Select Query « Oracle PL / SQL

Related examples in the same category