Oracle study Note 6

The last time I took a note, I learned how to query multiple tables. This time I took a note to learn about grouping functions in Oracle.

First, let's take a look at what group functions are:

Group function: acts on a group of data and returns a value for a group of data.

Type of group functions in SQL:

COUNT (): calculates the number of all records.

MAX (): calculate the maximum value in a group of data.

MIN (): calculates the minimum value.

AVG (): calculates the average value.

SUM (): SUM.

The AVG () and SUM () functions can be used for numeric data. The MIN () and MAX () functions are applicable to any data type, but generally apply to numeric data types.

Basic Syntax:

SELECT [column], group_function (column ),...

FROM table

[WHERE condition]

Example:Use the count () function to obtain the total number of records in the employee table.

SQL> select count (*) from emp;
 
COUNT (*)
----------
14
 
SQL> select count (deptno) from emp;
 
COUNT (EMPNO)
------------
14

In the count () function brackets, you can use *, * to return the total number of all records. You can use a column name. The column name indicates the total number of records that are not empty in the column record. In the preceding results, the Department number is queried, and 14 data entries are displayed in the result. If there are only 4 data entries in the department table, the returned query results are duplicated. You can use the distinct keyword to repeat. That is: select count (distinct deptno) from emp;, the returned result is 3.

Example:Use MAX and MIN to obtain the maximum wage and minimum wage of the employee in the employee table.

SQL> select max (sal), min (sal) from emp;
 
MAX (SAL) MIN (SAL)
--------------------
5000 800

Example:Use the AVG and SUM functions to calculate the average and total wages of Department 30 in the employee table.

SQL> select sum (sal), avg (sal) from emp where deptno = 30;
 
SUM (SAL) AVG (SAL)
--------------------
9400 1566.66666

Use the group by clause to group data.

Basic Syntax:

SELECT [column,] group_function (column ),...

FROM table

[WHERE condition]

[Group by column]

[Order by column asc | desc];

For example, calculate the number of employees in each department in the employee table.

SQL> select deptno, count (deptno)
2 from emp
3 group by deptno;
 
Deptno count (DEPTNO)
-------------------
30 6
20 5
10 3

Group data by deptno, and then count the total number of records in each group. And return the result.

For example, calculate the average salary of each department and the result is rounded up.

SQL> select round (avg (sal), deptno from emp group by deptno;
 
ROUND (AVG (SAL) DEPTNO
---------------------
1567 30
2175 20
2917 10

Note:

The words following the select statement can be followed by the column name in addition to functions. If it is the same as the column name after group. Otherwise the ORA-00979: Not a group by expression error occurs.

Example:

Select deptno, empno, count (empno)
From emp
Group by deptno
 
ORA-00979: Not a group by expression, empno is a redundant column.

 

While

Select deptno, count (empno)
From emp
Group by deptno; executed correctly.

For example, for a department with an average salary greater than 200

Select deptno
From emp
Where avg (sal)> 2000
Group by deptno
 
ORA-00934: grouping functions are not allowed here

When we execute the preceding SQL statement, the system prompts that grouping functions are not allowed after where. That is, grouping functions cannot appear after where statements. If you want to add restrictions to grouping functions, use the HAVING clause. The HAVING clause must be used with the group by clause. It can be placed before group by or after the house. Group by can be used independently.

That is:

Select deptno
From emp
Having avg (sal) & gt; 2000
Group by deptno

Result:

DEPTNO
------
20
10

For example, query the maximum salary of a department in average work.

SQL> select max (avg (sal) from emp group by deptno;
 
MAX (AVG (SAL ))
-------------
2916.66666666

The preceding results show that group functions can be nested. However, the deptno field name cannot appear after the select statement; otherwise, the ORA-00937: not a single component group function prompt appears.