Oracle Group functions

One, group function nesting

As defined in Oracle, group functions can be nested only two layers. In fact, multi-layer nesting has no practical purpose, so Oracle does not provide multi-layered nesting of group functions. However, a single-line function can be nested multiple layers.

Two

1. Oracle contains the following group functions
-avg ([distinct| All]n) return average, ignoring null values
-count ({*|[ Distinct| ALL]}EXPR) returns the number of records, with * contains null values, otherwise does not contain null values
-max ([distinct| ALL]EXPR) returns the maximum value, ignoring the null value
-min ([distinct| ALL]EXPR) returns the minimum value, ignoring the null value
-sum ([distinct| All]n) returns the total value, ignoring null values
-stddev ([distinct| ALL]X) return standard deviation, ignoring null values
-variance ([distinct| ALL]X) returns the statistical variance, ignoring null values

2. Usage of AVG and sum
-You can use AVG and sum for numeric data
Cases:
SELECT AVG (SAL), SUM (SAL)
From EMP
WHERE job like ' sales% ';

3. Usage of min and Max
-min and Max can be used with any data type
Cases:
SELECT MIN (HireDate), MAX (HireDate)
from EMP;
Results:
MIN (HireDate) MAX (hiredate)
1 July-December-80 2 March-May-87

4.

Usage of Count
1) Count (*) returns the total number of rows in the table
Cases:
SELECT COUNT (*)
From EMP
WHERE deptno=30;
Results:
COUNT (*)
6
2) count (expr) returns the number of non-empty rows
Cases:
SELECT COUNT (Comm)
From EMP
WHERE deptno=30;
Results:
COUNT (*)
4

5. Group functions and Null values
1) The group function ignores null values in the column
Cases:
SELECT AVG (Comm)
from EMP;
Results:
AVG (COMM)
550
2) The NVL function enables a group function to force a record containing a null value
Cases:
SELECT AVG (NVL (comm,0))
from EMP;
Results:
AVG (NVL (comm,0))
157.142857

6. Create a Data group
SELECT column,group_function (column)
From table
[WHERE condition]
[GROUP by Group_by_expression]
[Oeder by column];

-Divide the records in a table into groups by using the GROUP BY clause
-The specified column must be included in the GROUP by clause
-column aliases cannot be used in a GROUP by clause
-When using the GROUP BY clause, the Orale server automatically sorts the result collection by default by the column specified by the GROUP BY clause
-in the select list, all columns must be included in the GROUP BY clause in addition to the groups function
Cases:
SELECT Deptno,avg (SAL)
From EMP
GROUP by Deptno;
Results:
DEPTNO AVG (SAL)
10 2916.66667
20 2175
30 1566.66667
-the column specified by the GROUP by does not have to appear in the select list
Cases:
SELECT AVG (SAL)
From EMP
GROUP by Deptno;
Results:
AVG (SAL)
2916.66667
2175
1566.66667

7. Grouping by multiple columns
Cases:
SELECT Deptno,job,sum (SAL)
From EMP
GROUP by Deptno,job;
Results:
DEPTNO JOB SUM (SAL)
Ten Clerk 1300
Ten MANAGER 2450
Ten President 5000
1900 Clerk
ANALYST 6000
MANAGER 2975
950 Clerk
MANAGER 2850
5600 salesman

8. Illegal query using Group functions
Cases:
SELECT Dept,count (ename)
from EMP;
Results:
ERROR on line 1th:
ORA-00937: Non-single-group grouping functions
Correct:
SELECT Dept,count (ename)
From EMP
GROUP by Deptno;
Results:
DEPTNO COUNT (ename)
10 3
20 5
30 6

9. Limit Group Results
Restricting groups with a HAVING clause
-Grouping Records
-Apply group functions on a group basis
-Results that match the HAVING clause are not output

SELECT column,group_function
From table
[WHERE condition]
[GROUP by Group_by_expression]
[Having group_condition]
[ORDER by column];

Example 1:
SELECT Deptno,max (SAL)
From EMP
WHERE Max (SAL) >2900
GROUP by Deptno;
Results:
ERROR on line 3rd:
ORA-00934: Grouping functions are not allowed here
Correct:
SELECT Deptno,max (SAL)
From EMP
GROUP by Deptno
Having Max (SAL) >2900;
Results:
DEPTNO MAX (SAL)
10 5000
20 3000
Example 2:
SELECT job,sum (SAL) PAYROLL
From EMP
WHERE job isn't like ' sales% '
GROUP by Job
Having SUM (SAL) >5000
ORDER by SUM (SAL);
Results:
JOB PAYROLL
ANALYST 6000
MANAGER 8275

10. Group function nesting
-unlike single-line functions, group functions can only be nested two layers
Example: Show the maximum value of average salary
SELECT Max (avg (SAL))
From EMP
GROUP by Deptno;
Results:
MAX (AVG (SAL))
2916.66667

Practice
1. Use the EMP table to show the maximum, minimum, sum, average salary for all employees

SELECT Ename,max (SAL), MIN (SAL), SUM (SAL), AVG (SAL)
from EMP;

2. Displays the number of different department numbers in the EMP table

SELECT COUNT (DISTINCT deptno)
from EMP;

3. Display the maximum, minimum, sum, average salary of the employee in the EMP table, grouped by job column

SELECT Job,max (SAL), MIN (SAL), SUM (SAL), AVG (SAL)
From EMP
GROUP by Job;

4. Use the EMP table to display the job name, maximum, minimum, sum, average salary for each group
Requirements: Grouped by job column, the name of the ename column does not start with a, and the minimum salary for any group is greater than 1600

SELECT Job,max (SAL), MIN (SAL), SUM (SAL), AVG (SAL)
From EMP
WHERE ename not like ' A% '
GROUP by Job
Having MIN (SAL) >1600

5. Display the department name and the accumulated salary of each department, requiring the cumulative salary of each department to be greater than 3000

SELECT Dname,sum (SAL)
From EMP e,dept D
WHERE E.deptno=d.deptno
GROUP by Dname
Having SUM (SAL) >3000;

6. Show the highest and lowest salary for each department, each position

SELECT Deptno,job,max (SAL), MIN (SAL)
From EMP
GROUP by Deptno,job;

Three, SQL aggregation function nesting problems

See http://bbs.csdn.net/topics/390462360

1, the question: I now want to check is top client (the sum of things combined to add up)

So I lost. SELECT C.clientno, C.cname, MAX (SUM (P.amount))
From CLIENT c,purchase P
WHERE C.clientno=p.clientno
GROUP by C.clientno,c.cname;
The hint is not a single group of grouping functions, so the question should be on Max (P.amount), how should I write it separately?

Answer:

with t  as   ( SELECT job, deptno,  sum (sal) sal  FROM emp  group by job, deptno) select *  from t  where sal = ( select max (sal)  from t)2, the requirements show: the total department of Wages (SAL) the highest department number (DEPTNO) and the exact total number of wages.

I mean, there could be 10 or N departments, each department has a total wage, and then we need to find the department with the highest total wage.
Answer:

Select A.deptno, A.sumsal




Where RK < 2

Oracle Group functions