Sum() function and having clause : SUM « Aggregate Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Aggregate Functions
3. SUM

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  WHERE pro_id = 1001 OR pro_id=1002
  4  GROUP BY emp_id, pro_id
  5  HAVING SUM(hours_logged) > 20;
       101       1001
       102       1002
       106       1001
       107       1002

4 rows selected.

SQL>
SQL>
SQL> drop table server_usage;

Table dropped.