Group Statistics Query (study notes)

Find out the sum of the monthly payroll expenses of the company

-- find out the sum of the monthly payroll expenses of the company SELECT SUM  from EMP;

Check out the company's maximum wage, minimum wage and average wage

-- Check out the company's maximum wage, minimum wage and average wage SELECT MAX (SAL),MIN(sal),ROUND(AVG(SAL),2 from EMP;

Date of employment of the company's earliest employment and latest employment

-- date of employment of the company's earliest employment and latest employment SELECT MIN MAX  from EMP;

Statistic The median wage among the company's wages

-- statistic The median wage among the company's wages SELECT  from EMP;

Verify usage differences for COUNT (*), Count (field), Count (DISTINCT fields)

-- verify usage differences for COUNT (*), Count (field), Count (DISTINCT fields) SELECT COUNT (*countcount Count (DISTINCT from EMP;

Grouping Statistics Syntax

SELECT [DISTINCT]Grouping fields[ as] [Column Aliases],...|Statistical functions[ as] [aliases], ... .. fromTable Name 1[Table Aliases 1], table Name 2[Table Aliases 2] ....[WHERE condition (s)]     [GROUP by Group Field]    [Order by sort field asc| DESC];

Single- field grouping statisticscount the number of people in each department

-- count the number of people in each department SELECT COUNT   from empGROUP by

To count the minimum and maximum wage for each position

-- to count the minimum and maximum wage for each position SELECT Job,min(sal) minimum wage,max(sal) maximum wage   from GROUP by Job;

Find out the name of each department, the number of departments, the average salary of the Department, Service life

--Find out the name of each department, the number of departments, the average salary of the Department, Service lifeSELECTD.dname,COUNT(E.empno),ROUND(AVG(E.sal),2),ROUND(AVG(Months_between (Sysdate,e.hiredate)/ A),2) fromEMP e,dept DWHEREE.deptno (+)=D.deptnoGROUP  byD.dname;

Find out the number of employees and the average wage of each salary level of the company.

-- find out the number of employees and the average wage of each salary level of the company.  SELECT s.grade,COUNT(e.empno),ROUND (AVG(e.sal),2)  from EMP E,salgrade s WHERE between  and S.hisal GROUP  by S.grade;

Statistics on average salary of employees receiving commissions and non-Commission fees, number of employees

--statistics on the average salary of employees who receive Commission and non-Commission,--comm the number of employees is empty content can not be directly grouped, using a set to completeSELECT ROUND(AVG(E.sal),2),ROUND(AVG(Months_between (Sysdate,e.hiredate)/ A),2),COUNT(e.empno) fromEMP EWHEREE.comm is NULLUNION SELECT ROUND(AVG(E.sal),2),ROUND(AVG(Months_between (Sysdate,e.hiredate)/ A),2),COUNT(e.empno) fromEMP EWHEREE.comm is  not NULL;

Multi-field grouping statistics

--GrammarSELECT [DISTINCT]Group Field 1[ as] [Column Aliases],[Group Field 2 [ as] [Column Aliases], ...]|Statistical functions[ as] [aliases], ... .. fromTable Name 1[Table Aliases 1], table Name 2[Table Aliases 2] ....[WHERE condition (s)]     [Group BY Group Field 1, Group field 2, ....]    [Order by sort field asc| DESC];

Now ask for detailed information about each department

-- We now ask for detailed information about each department, department number, department name, department position, department number, average salary, total wage, maximum and minimum wage . SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal),SUM(sal),MAX (SAL),MIN(sal) from dept d,emp eWHERE d.deptno= E.deptno (+)GROUP by D.deptno,d.dname,d.loc;

Having clauseThe GROUP BY clause enables grouping of data, but in many cases it is often necessary to filter the data after grouping and then filter the data by statistical results, and to achieve such a function can only be done through having clauses. Syntax:

SELECT [DISTINCT]Group Field 1[ as] [Column Aliases],[Group Field 2 [ as] [Column Aliases], ...]|Statistical functions[ as] [aliases], ... .. fromTable Name 1[Table Aliases 1], table Name 2[Table Aliases 2] ....[WHERE condition (s)]     [Group BY Group Field 1, Group field 2, ....]    [Having filter conditions (s)]    [Order by sort field asc| DESC];

Find out all job information, average salary, number of employees with average salary greater than 2000

-- Find out all job information, average salary, number of employees with average salary greater than 2000 SELECT e.job,AVG(e.sal),COUNT(e.empno) from emp eGROUP   by e.jobhaveAVG(e.sal)>;

List all department numbers and names for at least one employee, and count the average wage, minimum wage, and maximum wage for these departments.

--List all department numbers and names for at least one employee, and count the average wage, minimum wage, and maximum wage for these departments. SELECTD.deptno,d.dname,AVG(e.sal) Avgsal,MIN(E.sal),MAX(E.sal),COUNT(e.empno) Number of persons fromEMP e,dept DWHEREE.deptno (+)=D.deptnoGROUP  byD.deptno,d.dname having COUNT(E.empno)>0;

Displays the sum of the non-salesperson's job names and monthly wages for the same working employee,
And to meet the total monthly wage of employees engaged in the same job is greater than $5000, the output is sorted by the total monthly wage

--Displays the sum of the non-salesperson's job names and monthly wages for the same working employee,--and to meet the total monthly wage of employees engaged in the same job is greater than $5000, the output is sorted by the total monthly wageSELECTE.job,SUM(e.sal) sumsal fromEMP EWHEREE.job<>'salesman' GROUP  byE.job having SUM(e.sal)> theORDER  bySumsalASC;

Having clauses are used after grouping, primarily to filter the results of a grouping

Group Statistics Query (study notes)