Database (grouping function)

Grouping function: operate on a group of records in the Table. Only one result is returned each time. Common functions include MIN, MAX, SUM, AVG, COUNT, and other MIN and MAX return the minimum and maximum values of each group. Eg: selectmin (salary), max (salary) fromemployees; the SUM and AVG functions return the SUM mean respectively. Eg: selectsum (salary), avg (salary)

Grouping function: operate on a group of records in the Table. Only one result is returned each time. Common functions include MIN, MAX, SUM, AVG, COUNT, and other MIN and MAX return the minimum and maximum values of each group. Eg: select min (salary), max (salary) from employees; the SUM and AVG functions return the SUM mean respectively. Eg: select sum (salary), avg (salary)

Grouping function: operate on a group of records in the Table. Only one result is returned each time.

Common functions include MIN, MAX, SUM, AVG, and COUNT.

MIN and MAX return the minimum and maximum values of each group.

Eg: select min (salary), max (salary) from employees;

The SUM and AVG functions return the average SUM respectively.

Eg: select sum (salary), avg (salary) from emloyees;

The count function mainly returns the number of records in each group.

Eg: select count (*) from employees where job_id = '';

Use of group by... HAVING clause.

The group function ignores NULL values. You can use the NVL, NVL2, and COALESCE functions to process null values.

Eg: average salary of employees with the same manager in the same position.

Select job_id, manager_id, avg (salary)

From employees

Group by job_id, manager_id

* Notes about the group by clause:

1. Columns after the group by clause can no longer appear in the SELECT statement.

2. The "non-grouping function" in the SELECT clause must appear in the group by clause.

HAVING clause.

Error example:

Select job_id, max (salary)

From employees

Where max (salary)> = 8888

Group by job_id;

The reason is that the execution order of the query statement is: from where group by select order

Correct example:

Select job_id, max (salary)

From employees

Group by job_id

Having max (salary) & gt; 8888;

Summary:
-Find the table to be queried using the FROM clause;
-Use the WHERE clause to filter and judge non-grouping functions;
-GROUP operations are performed using the group by clause;
-Use the HAVING clause to filter and judge group functions;
-Use the SELECT clause to SELECT the displayed columns, expressions, and group functions;
-Sort BY clauses.

Eg:

Select department_id, max (salary)

From employees

Where department_id between 30 and 90

Group by department_id

Having max (salary) & gt; 8888

Order by max (salary );

Zookeeper