04.SQL Basics--Grouping and grouping functions

First, group:

The grouping function can manipulate the rowset and give a result for each group. Use GROUP by Column1,column2,.. Group by Columm1,column2, that is, Column1,column2 combines the same value as a group

Second, commonly used grouping functions:

AVG ([distinct| All]n)--averaging, ignoring null value count ({*|[ Distinct| ALL]EXPR})--number of statistics, where expr is used to determine non-null values (using * to calculate all selected rows, including duplicate rows and rows with null values) MAX ([distinct| ALL]EXPR)--the maximum value, ignoring the null value min ([distinct| ALL]EXPR)--to find the minimum value, ignoring the null value sum ([distinct| All]n)--summing, ignoring null values

Third, the GROUPING function syntax: */

SELECT [Column,] group_function (column), ... From Table[where Condition][group by Column][order by column];

/*

Four, the use of grouping function guidelines:

DISTINCT  causes the function to consider only distinct values, and all takes into account all values, including duplicate values.　　The default is all. A       function with the expr parameter can have a data type of char,varchar2,number,date. All grouping functions ignore null values. You can use NVL,NVL2, or the COALESCE function instead of a null value when you use GROUP BY, the Oralce server implicitly sorts the result set in ascending order. You can use order by to change the sorting result. You can use the NVL function to force a grouping function to contain null values, such as: Select AVG (NVL (comm,0)) from EMP;

　　

The syntax of the GROUP by clause:

Use the GROUP BY clause to divide rows in a table into smaller groups, and then use grouping functions to return summary information for each group Select column, group_function (column) from Table[where Condition][group By Group_by_expression][order by column];
Group  by--group_by_expression which columns to group

Vi. GROUP by Use guidelines:

Columns that appear in the SELECT if they do not appear in the grouping function, the GROUP BY clause must contain these columns WHERE clauses can be excluded from grouping by using column aliases by default in group by list columns in ascending group by Columns may not appear in the group

Seven, packet filter:

Use the HAVING clause
Having used:   rows have been grouped   using group functions that meet the   conditions in the HAVING clause will be displayed

VIII. Demo: */

--Using the AVG and Sum methods for digital data

Select min (sal) as Min_sal,max (SAL) as Max_sal,
AVG (SAL) as Avg_sal,sum (SAL) as Sum_sal
From Scott.emp;

idle> Select min (sal) as Min_sal,max (SAL) as Max_sal,avg (SAL) as Avg_sal,sum (SAL) as Sum_salfrom scott.emp;  2    3     min_sal    max_sal avg_sal    sum_sal----------------------------------------       800 5000 2073.21429      29025

--for numbers, characters and date data types, you can use the Min and Max methods

Select min (hiredate) as Min_hiredate,max (HireDate) as max_hiredate from Scott.emp;

idle> Select min (hiredate) as Min_hiredate,max (HireDate) as max_hiredate from Scott.emp; Min_hireda Max_hireda--------------------1980-12-17 1987-05-23

--Use COUNT (*), count (expr), count (distinct expr)

--Note that Coung (*) contains null values, duplicate values, count (expr) filters null values, COUNT (distinct expr) filters null values, and also filters duplicate values

Sql> Select COUNT (*), COUNT (Mgr), COUNT (distinct MGR) from EMP;

Idle>  Select COUNT (*), COUNT (Mgr), COUNT (distinct MGR) from EMP;  COUNT (*) count (MGR) count (distinctmgr)--------------------------------------       6

--Use the NVL function to force the grouping function to contain null values

Sql>

idle>  Select AVG (comm), AVG (NVL (comm,0)) from EMP; AVG (COMM) AVG (NVL (comm,0))--------------------------       550 157.142857

--Grouping by using the GROUP BY clause

Sql> Select Job, avg (SAL) from the EMP group by job;

Idle> Select Job, avg (SAL) from the EMP group by job; JOB    AVG (SAL)-------------------clerk      1037.5salesman1400president5000manager   2758.33333ANALYST 3000

--group by columns may not appear in the group

Sql> Select AVG (SAL) from the EMP group by Job ORDER by AVG (SAL) desc;

Idle> Select AVG (SAL) from the EMP group by Job ORDER by AVG (SAL) desc;  AVG (SAL)----------      30002758.33333      1400    1037.5

　　

--incorrect usage, some columns in SELECT are not present in the GROUP BY clause

Sql> Select Job,avg (sal) from EMP;

Select Job,avg (SAL) from EMP

*

ERROR at line 1:

Ora-00937:not a single-group group function

--Use the HAVING clause to filter the grouped results

-Query The department number above the average wage and its average wage.

Select Deptno,avg (SAL) from EMP Group BY DEPTNO have avg (SAL) >2000;

Idle> Select Deptno,avg (SAL) from EMP Group BY DEPTNO have avg (SAL) >2000;    DEPTNO   AVG (SAL)--------------------20 217510 2916.66667

　　

--to find out the average wage in the above jobs (job)

Select Job,avg (SAL) from EMP Group BY job have avg (SAL) >2000;

Idle> Select Job,avg (SAL) from EMP Group BY job have avg (SAL) >2000; JOB    AVG (SAL)-------------------president5000manager   2758.33333ANALYST 3000

　　

-The number of people in the Department of the above

Select Deptno,count (*) from EMP GROUP by DEPTNO have Count (*) >5;

Idle> Select Deptno,count (*) from the EMP group by DEPTNO have Count (*) >5;    DEPTNO   COUNT (*)--------------------    6

　　

--using nesting of grouping functions

Sql> Select MAX (avg (SAL)) from the EMP group by DEPTNO;

Idle> Select MAX (avg (SAL)) from the EMP group by DEPTNO; MAX (SAL)-------------   2916.66667

　

04.SQL Basics--Grouping and grouping functions