Oracle grouping function usage example

Aggregate functions, multiline functions, and grouping functions are all types of functions.
Group by and HAVING

Group function: AVG \ SUM \ MIN \ MAX \ COUNT \ STDDEV \ VARIANCE
Use DISTINCT in combination with the group Function
Processing of NULL values in the group Function

Nested group Function

Syntax of the group function:
SELECT GROUP_FUNCTION (COLUMN ),...
FROM TABLE
[Where condition]
[Order by column];

--- Example 1: AVG \ MAX \ MIN \ SUM for NUMBER type data

Select avg (SALARY), MAX (SALARY), MIN (SALARY), SUM (SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '% REP % ';

AVG (SALARY) MAX (SALARY) MIN (SALARY) SUM (SALARY)
--------------------------------------------
8272.72727 11500 6000 273000
 
--- Example 2: MIN and MAX can be used for data of the date type outside the number type.
Hr @ PROD> select min (HIRE_DATE), MAX (HIRE_DATE) from employees;

MIN (HIRE _ MAX (HIRE _
------------------
17-JUN-87 21-APR-00

---- Example 3: COUNT (*), COUNT (1), and COUNT (1) are faster than COUNT (*).
Hr @ PROD> select count (*) from employees;

COUNT (*)
----------
107
----- COUNT (*) returns the number of rows in a table.
Hr @ PROD> select count (1) from employees;

COUNT (1)
----------
107

--- COUNT (EXPR) indicates the number of rows that meet all non-null values of expr. See the following example:
Hr @ PROD> select count (COMMISSION_PCT) from employees;

COUNT (COMMISSION_PCT)
---------------------
35

 

Hr @ PROD> select count (DEPARTMENT_ID) from employees;

COUNT (DEPARTMENT_ID)
--------------------
106

Hr @ PROD> select count (EMPLOYEE_ID) from employees;

COUNT (EMPLOYEE_ID)
------------------
107

------------ Use of DISTINCT and group functions
Example:
Hr @ PROD> select count (DISTINCT DEPARTMENT_ID) from employees;

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

--------------------------------
----------- Processing of Null values by the group Function
---- The group function ignores the null value in the column.

Hr @ PROD> select count (COMMISSION_PCT) from employees;

COUNT (COMMISSION_PCT)
---------------------
35

 

Hr @ PROD> select count (NVL (COMMISSION_PCT, 0) from employees;

COUNT (NVL (COMMISSION_PCT, 0 ))
----------------------------
107

----- 35 people participate in computing
Hr @ PROD> select avg (COMMISSION_PCT) from employees;

AVG (COMMISSION_PCT)
-------------------
. 222857143

------ 107 people involved in computing
Hr @ PROD> select avg (NVL (COMMISSION_PCT, 0) from employees;

AVG (NVL (COMMISSION_PCT, 0 ))
--------------------------
. 072897196

------- Create group data ----
Group by clause

Calculate the average salary of each department
Select column, GROUP_FUNCTION (COLUMN)
FROM TABLE
[Where condition]
[Group by GROUP_BY_EXPRESSION]
[Order by column];

Note: The COLUMN in the SELECT clause must be included in the group by clause.
The listed rows must be included in the group by clause.
Execution order: Calculate the WHERE clause first, then calculate group by, then query the result, and finally execute order
Aliases can be used in order by. aliases are not allowed in where and group.

1 2 Next Page