📋Steps in this guide1/5

SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause

In this article we take a look at the GROUP BY clause and HAVING clause in SQL.

oracle miscconfigurationintermediate

by OracleDba

13 views

Setup

You can perform all these queries online for free using SQL Fiddle . The examples in this article require the following tables to be present. These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Code/Command (click line numbers to comment):

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

GROUP BY Clause

SQL includes many Aggregate Functions , which take information from multiple rows and aggregate it down to produce fewer rows in the final result set. The following code contains a simple example of that using the , and aggregate functions. We have 14 rows in the table, but the query produces a single row with the count of the rows in the table, as well as the the average and total of the column for all rows in the table. Without a clause, the whole result set is treated as a single group, so the aggregate functions work on the result set as a whole. Adding the clause splits the results into groups of rows, with the aggregate functions being applied on a group basis. The following example groups the rows by DEPARTMENT_ID, so the aggregates are on a per-department basis. The more columns included in the clause, the more granular the aggregates. The following example groups by the DEPARTMENT_ID and JOB columns. Remember, all non-aggregate columns must be included in the clause.

Code/Command (click line numbers to comment):

SELECT
COUNT(*)
AS employee_count,
AVG(e.salary)
AS avg_salary,
SUM(e.salary)
AS sum_salary
FROM   employees e;

EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
            14 2073.21429      29025

1 row selected.

SQL>

SELECT e.department_id,
       COUNT(*) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   employees e
GROUP BY e.department_id
ORDER BY e.department_id;

DEPARTMENT_ID EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- -------------- ---------- ----------
           10              3 2916.66667       8750
           20              5       2175      10875
           30              6 1566.66667       9400

3 rows selected.

SQL>

SELECT e.department_id,
       e.job,
       COUNT(*) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   employees e
GROUP BY e.department_id, e.job
ORDER BY e.department_id, e.job;

DEPARTMENT_ID JOB       EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- --------- -------------- ---------- ----------
           10 CLERK                  1       1300       1300
           10 MANAGER                1       2450       2450
           10 PRESIDENT              1       5000       5000
           20 ANALYST                2       3000       6000
           20 CLERK                  2        950       1900
           20 MANAGER                1       2975       2975
           30 CLERK                  1        950        950
           30 MANAGER                1       2850       2850
           30 SALESMAN               4       1400       5600

9 rows selected.

SQL>

Joins

A grouped query can join to other tables to provide additional information. In the following example we want to report some aggregate data based on department, but we want to display the DEPARTMENT_NAME, rather than the DEPARTMENT_ID, so we have to join to the DEPARTMENTS tables. This works fine, but we are losing an important piece of information. The OPERATIONS department has no employees, but we don't display it because we have used an . Switching to a appears to fix that problem, but it reports the wrong number of employees. Now we have a row for the OPERATIONS department, the call has correctly returned the value 1, but this is not the correct number of employees. This is not the fault of the , but the way the call works. If instead we reference a mandatory column from the outer-joined table in the call, we will get the correct result, as NULL values are not counted. The following example shows how.

Code/Command (click line numbers to comment):

SELECT d.department_name,
       COUNT(*) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM
departments d
       JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING
3 2916.66667       8750
RESEARCH
5       2175      10875
SALES
6 1566.66667       9400

3 rows selected.

SQL>

SELECT d.department_name,
       COUNT(*) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING                  3 2916.66667       8750
OPERATIONS                  1
RESEARCH                    5       2175      10875
SALES                       6 1566.66667       9400

4 rows selected.

SQL>

SELECT d.department_name,
COUNT(e.employee_id)
AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING                  3 2916.66667       8750
OPERATIONS                  0
RESEARCH                    5       2175      10875
SALES                       6 1566.66667       9400

4 rows selected.

SQL>

HAVING Clause

We mentioned previously, having more columns referenced in the clause gives us more granular information. The following example groups the data by DEPARTMENT_NAME and JOB. Sometimes we are only interested in information from specific groups of data. The clause allows us to filter out groups that do not meet a specific requirement. You can think of it as a clause for the clause. In the following example we are only returning information for groups with more than 1 employee. Just as a silly aside, it is possible to have a clause without a clause, as shown in the following example. The whole result set is treated as a single group, so the clause filer can still be applied.

Code/Command (click line numbers to comment):

SELECT d.department_name, e.job,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
ORDER BY d.department_name, e.job;

DEPARTMENT_NAM JOB       EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
ACCOUNTING     CLERK                  1       1300       1300
ACCOUNTING     MANAGER                1       2450       2450
ACCOUNTING     PRESIDENT              1       5000       5000
OPERATIONS                            0
RESEARCH       ANALYST                2       3000       6000
RESEARCH       CLERK                  2        950       1900
RESEARCH       MANAGER                1       2975       2975
SALES          CLERK                  1        950        950
SALES          MANAGER                1       2850       2850
SALES          SALESMAN               4       1400       5600

10 rows selected.

SQL>

SELECT d.department_name, e.job,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
HAVING COUNT(e.employee_id) > 1
ORDER BY d.department_name, e.job;

DEPARTMENT_NAM JOB       EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
RESEARCH       ANALYST
2
3000       6000
RESEARCH       CLERK
2
950       1900
SALES          SALESMAN
4
1400       5600

3 rows selected.

SQL>

SELECT COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS sum_salary
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
HAVING COUNT(e.employee_id) > 2
ORDER BY d.department_name, e.job;

EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
            14 2073.21429      29025

1 row selected.

SQL>

23ai Updates

From Oracle 23ai onward we can use the column alias in GROUP BY and HAVING clauses, or the column position in the GROUP BY clause. This functionality is demonstrated here . For more information see: - SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause - SQL for Beginners - Full Playlist - Oracle SQL Articles - Getting Started - ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23ai - GROUP BY ALL in Oracle Database 23ai - Database SQL Language Reference : SELECT - Aggregate Functions Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!