Oracle Database 12c: SQL Workshop I: 06 Reporting Aggregated Data Using the Group Functions

Reporting Aggregated Data Using the Group Functions

Group functions

Group functions operate on sets of rows to give one result per
group

The COUNT function has three formats:
• COUNT(*)
• COUNT(expr)
• COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns.
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr

SQL> select * from t4;

    ID     NO
---------- ----------
     2
            3
     1      2
     1      1

SQL> select count(*) from t4;

  COUNT(*)
----------
     4

SQL> select count(id) from t4;

 COUNT(ID)
----------
     3

SQL> select count(distinct id) from t4;

COUNT(DISTINCTID)
-----------------
        2

Creating Groups of Data

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

• If you include a group function in a SELECT clause, you cannot select individual column as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
• Using a WHERE clause, you can exclude rows before dividing them into groups.
• You can substitute column by an Expression in the SELECT statement.
• You must include the columns in the GROUP BY clause.
• You cannot use a column alias in the GROUP BY clause.
• All the columns in the SELECT list that are not in group functions must be in the GROUP BY clause.
• The GROUP BY column does not have to be in the SELECT list.

SELECT department_id, job_id, sum(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY job_id;

DEPARTMENT_ID JOB_ID           SUM(SALARY)
------------- -------------------- -----------
      110 AC_ACCOUNT         20000
      110 AC_MGR             20000
       10 AD_ASST            20000
       90 AD_PRES            22000
       90 AD_VP          40000
      100 FI_ACCOUNT        100000
      100 FI_MGR             20000
       40 HR_REP             20000
       60 IT_PROG           100000
       20 MK_MAN             20000
       20 MK_REP             20000

DEPARTMENT_ID JOB_ID           SUM(SALARY)
------------- -------------------- -----------
       70 PR_REP             20000
       30 PU_CLERK          100000
       30 PU_MAN             20000
       80 SA_MAN            100000
       80 SA_REP            580000
          SA_REP             20000
       50 SH_CLERK          400000
       50 ST_CLERK          400000
       50 ST_MAN             84000

20 rows selected.

• You cannot use the WHERE clause to restrict groups.
• You use the HAVING clause to restrict groups.
• You cannot use group functions in the WHERE clause

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
  4  GROUP BY department_id;
WHERE AVG(salary) > 8000
      *
ERROR at line 3:
ORA-00934: group function is not allowed here


SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
  4  HAVING AVG(salary) > 8000;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
      100       20000
       30       20000
            20000
       20       20000
       70       20000
       90  20666.6667
      110       20000
       50  19644.4444
       40       20000
       80       20000
       10       20000

DEPARTMENT_ID AVG(SALARY)
------------- -----------
       60       20000

12 rows selected.

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions
are calculated before the HAVING clause is applied to the groups in the SELECT list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

Nesting Group Functions

Group functions can be nested to a depth of two functions