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.

All columns in the select list must be included in
Group by clause.

Example: calculate the average salary of each department by group of departments
Select department_id, AVG (salary)
From employees
Group by department_id
Order by department_id;

Department_id AVG (salary)
------------------------
10 4400
20 9500
30 4150
40 6500
50 3475.55556
60 5760
70 10000
80 8955.88235
90 19333.3333
100 8600
110 10150
7000

12 rows selected.

------------- Important: advanced usage
Use group by to group multiple columns
Select department_id dept_id, job_id, sum (salary)
From employees
Group by department_id, job_id
Order by department_id;

Dept_id job_id sum (salary)
-------------------------------
10 ad_asst 4400
20 mk_man 13000
20 mk_rep 6000
30 pu_clerk 13900
30 pu_man 11000
40 hr_rep 6500
50 sh_clerk 64300
50 st_clerk 55700
50 st_man 36400
60 it_prog 28800
70 pr_rep 10000
80 sa_man 61000
80 sa_rep 243500
90 ad_pres 24000
90 ad_vp 34000
100 fi_account 39600
100 fi_mgr 12000
110 ac_account 8300
110 ac_mgr 12000
Safety _ rep 7000

------- Example of invalid group function usage:
Example 1:
HR @ prod> select department_id, count (last_name)
2 from employees;
Select department_id, count (last_name)
*
Error at line 1:
ORA-00937: not a single-group Function

------ A group by clause must be added, including department_id

Example 2:
HR @ prod> select department_id, job_id, count (last_name)
2 from employees
3 group by department_id;
Select department_id, job_id, count (last_name)
*
Error at line 1:
ORA-00979: Not a group by expression

------- Job_id must be added to the group by clause.

------- Do not restrict groups in the where clause
You can use the having clause to limit the groups.
Select department_id, AVG (salary)
From employees
Where AVG (salary) & gt; 8000
Group by department_id;

Where AVG (salary) & gt; 8000
*
Error at line 3:
The ORA-00934: group function is not allowed here

------------------------------------
--------- Having clause
Syntax: First group by, then group function, then having
Select column, group_function
From table
[Where condition]
[Group by group_by_expression]
[Having group_condition]
[Order by column];

Example: Having clause
Select department_id, max (salary)
From employees
Group by department_id
Having max (salary) & gt; 10000;

Department_id max (salary)
------------------------
100 12000
30 11000
90 24000
20 13000
110 12000
80 14000

------- Execution sequence: Select \ from \ where \ group by \ having \ order

Select job_id, sum (salary) payroll
From employees
Where job_id not like '% rep %'
Group by job_id
Having sum (salary)> 13000
Order by sum (salary );

Job_id payroll
--------------------
Pu_clerk 13900
Ad_pres 24000
It_prog 28800
Ad_vp 34000
St_man 36400
Fi_account 39600
St_clerk 55700
Sa_man 61000
Sh_clerk 64300

------------- Nesting of group functions
Last example:
Select max (AVG (salary ))
From employees
Group by department_id;

Select max (AVG (salary ))
From employees

Group by department_id;

For reprint, please indicate the source of the author and original text; otherwise, you will not be allowed to reprint

Source: http://blog.csdn.net/xiangsir/article/details/8604794