Group Statistics Query

————— Group Query ——————

Group statistical query often refers to some of the data have some common
⑤ control the data column to display select[distinct]*| column name [alias]
① determining the data source from table name [alias]
② determining which data rows meet the criteria [WHERE filter condition (s)]
③ GROUP by grouping fields for data implementation, grouping fields
④ filter for grouped data after having group filter conditions
⑥ sort the order by field for the data structure [asc| DESC],...
Example: According to the occupational group, the name and average number of each position are counted. Average wage
SELECT Job,count (empno), AVG (SAL)
From EMP
GROUP by Job;
Example: The number of each department and the maximum minimum wage of each department are queried.
SELECT Deptno,count (empno), MAX (SAL), MIN (SAL)
From EMP
GROUP by Deptno;
Duplicate data exists for both job and EMP
Limit 1: When the GROUP BY clause is not written (all tables as a group), only the SELECT clause allows
A statistical function appears, and no other fields are allowed:
-The wrong code
Selcet COUNT (Emono), ename from EMP;
-The correct code
Selcet COUNT (Emono) from EMP;
Limit 2: When using group by words, only grouped fields and statistical functions are allowed in the select sentence, and other fields are not allowed to appear
-The correct code
SELECT Job,count (empno) from the EMP group by Job;//job are grouped fields
-The wrong code
SELECT Job,ename,count (empno) from the EMP GROUP by job;
Limit 3: Statistical functions allow nested queries, but in a nested statistic query, no fields are allowed in the select query, including grouped fields, only nested statistical functions can be used
-The correct code
SELECT MAX (AVG (SAL))
From EMP
GROUP by Deptno;
-The wrong code
SELECT Deptno,max (AVG (SAL))
From EMP
Group BY deptno;//error reason is not a single group grouping functionGroup Multiple table queries

Example: Query the name, number of people, average salary of each department
Determine which data table to use
-dept Table: Department Name
-emp table: Statistics of the number of people, the average wage
Determining known associated fields
-Employee and Department: Emp.deptno = Dept.deptno.
1. First check the number of each employee, department name, salary
SELECT E.empno,d.dname,e.sal
From EMP e,dept D
WHERE E.deptno = D.deptno;
Duplicate data appears on the 2.dname field, and duplicate data can be grouped, grouped by department name, to group query results directly
SELECT D.dname,count (E.empno), AVG (SAL)
From EMP e,dept D
WHERE E.deptno = D.deptno
GROUP by D.dname;
3. A total of four departments, but now only three appear, joined the external connection control
SELECT D.dname,count (E.empno), AVG (SAL)
From EMP e,dept D
WHERE E.deptno (+) = D.deptno
GROUP by D.dname;
Example: Query the number, name, location, number of departments, average service life of each department
Determine which data table to use
-dept Table: Department number, name, location
-emp table: Number of departments, average length of service
Determining known associated fields
-Employee and Department: Emp.deptno = Dept.deptno.
1. First check the number, name, location, employee number, hire date of each employee
SELECT d.deptno,d.dname,d.loc,e.empno,e.hiredate
From EMP e,dept D
WHERE E.deptno (+) = D.deptno;
2. Through the above query can be found, at this time three fields (Deptno, Dname, loc) have duplicate data
You can use the multi-field grouping operation (only if the data for multiple columns is completely duplicated)
SELECT D.deptno,d.dname,d.loc,count (E.empno), AVG (Months_between (sysdate,e.hiredate)/12) year
From EMP e,dept D
WHERE E.deptno (+) = D.deptno
Group BY d.deptno,d.dname,d.loc;//only the items within the group by will appear inside the Select
Example: Asking for a job name and average salary above 2000 of the average salary
SELECT Job,avg (SAL)
From EMP
WHERE AVG (SAL) >2000
Group BY job;//error Reason: Grouping functions are not allowed here the grouping function is not allowed on the WHERE clause
Because statistics belong to the category after group by, where is used before the group by operation
SELECT Job,avg (SAL)
From EMP
Having AVG (SAL) >2000
GROUP by Job;the difference between where and having

Example: Statistics all non-salesperson job names and the sum of monthly wages for the same job employee, and the total monthly salary of employees who are engaged in the same job is greater than 5000, and the output is in ascending order of monthly wages
1. Show all non-salespeople
SELECT *
from EMP
WHERE job<> ' salesman ';
2. According to the work group, the sum of all wages
SELECT job,sum (sal)
from emp
WHERE job<> ' salesman '
GROUP by job;
3. The salary is greater than 5000 to be displayed in ascending order
SELECT job,sum (sal) SUM
from emp
WHERE job<> ' salesman '
GROUP by Job
Have sum (SAL) >5000
ORDER by SUM ASC;
Example: Statistics on the number of employees who receive Commission and non-Commission, average wage
SELECT comm,count (empno), AVG (SAL)
from EMP
Group by comm;//itself for group statistics, error SELECT ' Collect Commission ' Title,count (EMPNO), AVG (SAL) from EMP WHERE comm are not NULL
UNION
SELECT ' Do not receive Commission ' Title,count (empno ), AVG (SAL) from the EMP WHERE comm is NULL;
Query 1: The number of employees who receive commission, average salary;
SELECT ' Collect Commission ' Title,count (EMPNO), AVG (SAL) from EMP WHERE comm are not NULL;
Query 2: Query the number of employees who do not receive commission, average salary;
SELECT ' Do not claim Commission ' Title,count (EMPNO), AVG (SAL) from EMP WHERE comm is NULL; Summary

Group statistics before you look at the repeating column, if there are more than one write multiple fields
Use restrictions:
Grouping functions can not appear after nesting any fields
grouping functions may be used alone, if you use group by to use the
Multi-table query and group statistics , the result of the query is equivalent to a temporary table, and all groups are grouped in a temporary table using the