Group function and group statistics, group function statistics

Group function and group statistics, group function statistics

Grouping Functions

Grouping functions commonly used in SQL

Count (): Count

Max (): calculates the maximum value.

Min (): Minimum value

Avg (): calculates the average value.

Sum (): Sum

-- Count the number of people in the emp table select count (*) from emp; -- count the number of people who receive the bonus select count (comm) from emp; -- calculate the minimum wage of all employees select min (sal) from emp; -- select max (sal) from emp for the highest salary of all employees; -- select avg (sal) for the average salary and total salary of employees with a department number of 20 ), sum (sal) from emp where deptno = 20;

Group statistics Query

Syntax format

SELECT {DISTINCT} * | query column 1 alias 1, query Column 2 alias 2 ......

FORM table name 1 alias 1, table name 2 alias 2 ,......

{WHERE condition expression}

{Group by grouping condition}

{ORDERBY sorting field ASC | DESC, sorting field ASC | DESC ,......}

-- Calculate the number of people in each department: select deptno, count (empno) from emp group by deptno; -- Obtain the average salary of each department: select deptno, avg (sal) from emp group by deptno;

Calculates the highest salary for each department and the name of the employee who receives the highest salary

If you write

SELECT ename,max(sal) FROM emp GROUP BY deptno

Oracle will prompt row 1st with an error:

ORA-00979: Not a group by expression

The above code is incorrect during execution because:

1. If the grouping function is used in the program, the query results can be normal in the following two cases:

Group by exists in the program, and the grouping conditions are specified, so that the grouping conditions can be queried together.

If you do not use group by, you can only use GROUP functions independently.

2.When grouping functions are used, fields other than grouping functions and grouping conditions cannot appear in the query result column.

To sum up, we observe this rule when performing grouping statistics queries:

The field that appears in the field list. If it does not appear in the GROUP function, it must appear after the group by statement.

-- Calculate the highest salary of each department and the employee name of the highest salary: select deptno, ename, sal from emp where sal in (select max (sal) from emp group by deptno );

-- Query the Department name of each department and select d. dname, count (e. empno) from emp e, dept dwhere e. deptno = d. deptnogroup by d. dname

Find the Department numbers and average salaries with an average salary greater than 2000

It is easy for beginners to mistakenly write the conditions with a salary greater than 2000 after the where clause

SELECT deptno,avg(sal)  FROM emp  WHERE avg(sal)>2000   GROUP BYdeptno<span style="font-family:SimSun;"></span>

The following error message is displayed:

ORA-00934: grouping functions are not allowed here

-- Select e. deptno, avg (sal) from emp e, dept dwhere e. deptno = d. deptnohaving avg (sal)> 2000 group by e. deptno;

Rule: WHERE can only be used to filter a single record. having is used to filter groups.

The grouping function can only be used in groups and cannot appear in the WHERE statement. To specify grouping conditions, you can only use the command of the second condition: HAVING

-- Display the non-sales staff's work name and the total monthly salary of the same employee, and the total monthly salary of the employees engaged in the same job must be greater than $5000, output result: select e. job, sum (e. sal) sum_salfrom emp ewhere e. job <> 'salesman' group by e. jobhaving sum (e. sal)> 5000 order by sum_sal;

Simple grouping principles:
Only when duplicate content exists in a column can the group query be considered.

Note:

Grouping functions can be nested, but column names with grouping conditions cannot appear when grouping functions are nested.

For example, find the Department ID, Department name, and average department salary with the highest average wage

Step 1:

select deptno, avg(sal) from emp group by deptno;

Step 2:

select deptno, max(avg(sal)) from emp group by deptno;

ORA-00937: not a single component group Function

Step 3: remove the deptno column from the search results

select max(avg(sal)) from emp group by deptno;

After completion:

select d.deptno, d.dname, t.avg_sal  from dept d,   (select deptno,avg(sal) avg_sal      from emp       group by deptno having avg(sal)=          (select max(avg(sal)) from emp group by deptno)    ) twhere t.deptno=d.deptno;