Oracle grouping query details

I. What is a grouping function?

The grouping function acts on a group of data and returns a value for a group of data.

Ii. Grouping function types

Avg, count, max, min, stddev (standard variance), sum.

Function Name	Function Description
Count	Returns the number of records found.
Min	Returns the minimum value of a numeric column or computed column.
Max	Returns the maximum value of a numeric column or computed column.
Sum	Returns a numeric column or calculates the total number of columns.
Avg	Returns the average value of a numeric column or computed column.

 

3. Grouping function syntax

SELECT [Column,]Group_function (column ),...

FROMTable

[WHERECondition]

[GROUPColumn]

[ORDERColumn];

// The total number of returned records // * Indicates a record.

SQL> select count (*) from emp;

// Returns the total number of records whose comm is not empty.

SQL> select count (comm) from emp;

// COUNT (DISTINCT expr) returnsExprThe total number of non-empty and non-Repeated Records

SQL> select count (distinct (sal) from emp;

Note: Group functions ignore null values.

// Returns the average salary of all employees

SQL> select avg (nvl (sal, 0) from emp;

Note: The NVL function makes the grouping function unable to ignore null values.

// Returns the minimum employee ID.

SQL> select min (empno) from emp;

// Returns the maximum employee salary

SQL> select max (sal) from emp;

// Calculate the total salary issued by the company in the current month

SQL> select sum (comm) + sum (sal) from emp;

SQL> select sum (nvl (sal, 0) + nvl (comm, 0) from emp;

Group

If you want to group data by the value of a column during the query, you must use the group by clause to count the data in the group. The group by clause can be used no matter whether select uses the where clause.

Note: The group by clause must be used with the grouping function; otherwise, it does not make sense.

// Obtain the number of employees in each department

SQL> select deptno, count (*) as "count" from emp group by deptno;

// Obtain the average salary of employees in each department

SQL> select deptno, avg (nvl (sal, 0) from emp group by deptno;

// Note: columns in the group by clause do not need to be included in the SELECT list.

SQL> select avg (nvl (sal, 0) from emp group by deptno;

 

// Obtain the group by field of the number of employees in the same position in a department.

SQL> select deptno, job, count (*) from emp group by deptno, job order by deptno;

Having clause

The HAVING clause sets conditions for the group by clause in a similar way as the WHERE clause and SELECT statement. The WHERE clause search condition is applied before grouping, while the HAVING search condition is applied after grouping. HAVING syntax is similar to WHERE syntax, but HAVING can contain aggregate functions. HAVING clause can reference any item in the selection list.

Note: having clauses are usually used in combination with group by clauses.

 

Syntax:

SELECTColumn,Group_function

FROMTable

[WHERECondition]

[GROUPGroup_by_expression]

[HAVINGGroup_condition]

[ORDERColumn];

// Query the number of employees in a department greater than the number of five departments

SQL> select deptno, count (*) from emp group by deptno having count (*)> 5;