Grouping function of oracle

What is a grouping function
The grouping function acts on a group of data and returns a value to a group of data.

Group function type
AVG
COUNT
MAX
MIN
STDDEV
SUM

Group function syntax

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

AVG and SUM functions
AVG and SUM functions can be used for numeric data.

SQL> SELECT AVG(salary), MAX(salary),
  2                MIN(salary), SUM(salary)
  3  FROM   employees
  4  WHERE  job_id LIKE '%REP%';

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
8272.727272       11500        6000      273000

MIN and MAX functions
You can use the MIN and MAX functions for any data type.

SQL> SELECT MIN(hire_date), MAX(hire_date)
  2  FROM	  employees;

MIN(HIRE_DATE) MAX(HIRE_DATE)
-------------- --------------
1987/6/17      2000/4/21

COUNT function
COUNT(*) returns the total number of records in the table, applicable to any data type.

SQL> SELECT COUNT(*)
  2  FROM	  employees
  3  WHERE  department_id = 50;

  COUNT(*)
----------
        45

COUNT function
COUNT(expr) returns the total number of records whose expr is not empty.

SQL> SELECT COUNT(commission_pct)
  2  FROM   employees
  3  WHERE  department_id = 50;

COUNT(COMMISSION_PCT)
---------------------
                    0

Group functions and nulls
Group functions ignore null values.

SQL> SELECT AVG(commission_pct)
  2  FROM   employees;

AVG(COMMISSION_PCT)
-------------------
  0.222857142857143



SQL> Select avg(commission_pct),sum(commission_pct)/107,
  2  sum(commission_pct)/count(commission_pct)
  3  From employees;

AVG(COMMISSION_PCT) SUM(COMMISSION_PCT)/107 SUM(COMMISSION_PCT)/COUNT(COMMISSION_PCT)
------------------- ----------------------- -----------------------------------------
  0.222857142857143      0.0728971962616822                         0.222857142857143

Using NVL functions in group functions
The NVL function prevents grouping functions from ignoring null values.

SQL> SELECT AVG(NVL(commission_pct, 0))
  2  FROM   employees;

AVG(NVL(COMMISSION_PCT,0))
--------------------------
        0.0728971962616822

DISTINCT keyword
COUNT(DISTINCT expr) returns the total number of non empty and non duplicate records

SQL> SELECT COUNT(DISTINCT department_id)
  2  FROM   employees;

COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11

Grouping data

Grouping data: GROUP BY clause syntax
You can use the GROUP BY clause to divide the data in a table into groups

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

Clear: WHERE must be placed after FROM

GROUP BY clause
In the SELECT list, all columns that are not included in the group function should be included
In the GROUP BY clause.

SQL> SELECT   department_id, AVG(salary)
  2  FROM     employees
  3  GROUP BY department_id ;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
                     7000
          100        8600
           30        4150
           90 19333.33333
           20        9500
           70       10000
          110       10150
           50 3475.555555
           80 8955.882352
           40        6500
           60        5760
           10        4400

GROUP BY clause

SQL> SELECT   AVG(salary)
  2  FROM     employees
  3  GROUP BY department_id ;

AVG(SALARY)
-----------
       7000
       8600
       4150
19333.33333
       9500
      10000
      10150
3475.555555
8955.882352
       6500
       5760
       4400

12 rows selected

Group with multiple columns

Contains more than one column in the GROUP BY clause

SQL> SELECT   department_id dept_id, job_id, SUM(salary)
  2  FROM     employees
  3  GROUP BY department_id, job_id ;

DEPT_ID JOB_ID     SUM(SALARY)
------- ---------- -----------
    110 AC_ACCOUNT        8300
     90 AD_VP            34000
     50 ST_CLERK         55700
     80 SA_REP          243500
     50 ST_MAN           36400
     80 SA_MAN           61000
    110 AC_MGR           12000
     90 AD_PRES          24000
     60 IT_PROG          28800
     20 MK_MAN           13000
    100 FI_MGR           12000
     30 PU_CLERK         13900
     50 SH_CLERK         64300
    100 FI_ACCOUNT       39600
        SA_REP            7000
     70 PR_REP           10000
     10 AD_ASST           4400
     20 MK_REP            6000
     30 PU_MAN           11000
     40 HR_REP            6500

20 rows selected

Illegal use of group functions
All columns contained in the SELECT list but not in the group function are
Must be included in the GROUP BY clause.

SELECT department_id, COUNT(last_name)
FROM   employees;
SELECT department_id, COUNT(last_name)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Missing column in GROUP BY clause

Group functions cannot be used in the WHERE clause.
You can use group functions in the HAVING clause.

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

Group functions cannot be used in the WHERE clause

Filter grouping

Filtering groups: HAVING clause
Use HAVING to filter groups:

  1. Rows have been grouped.
  2. Group functions are used.
  3. Groups that meet the conditions in the HAVING clause are displayed.
SELECT	column, group_function
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[HAVING	group_condition]
[ORDER BY	column];

HAVING clause

SQL> SELECT   department_id, MAX(salary)
  2  FROM     employees
  3  GROUP BY department_id
  4  HAVING   MAX(salary)>10000 ;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
          100       12000
           30       11000
           90       24000
           20       13000
          110       12000
           80       14000
6 rows selected

Nested group function
Displays the maximum value of the average salary of each department

SQL> SELECT   MAX(AVG(salary))
  2  FROM     employees
  3  GROUP BY department_id;

MAX(AVG(SALARY))
----------------
19333.3333333333

Summary
Use group functions: avg(),sum(),max(),min(),count()
Use the GROUP BY clause in the query.
Use the HAVING clause in the query.

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

Tags: Database Oracle SQL

Posted on Mon, 20 Sep 2021 22:31:37 -0400 by brcolow