Oracle Learning 3

Inquire

1. GROUP BY

2. Basic functions

AVG (product mean) AVG (column_name) for this column of the product mean value

SUM (sum) sum (column_name) The sum of this column

Max (max) max (column_name) to find the maximum value in this column

Min (min) min (colunm_name) to find the minimum value of this column

Count (total) count (*), COUNT (column_name) number of rows in this column, COUNT (distinct column_name) to Redo row count

Wm_concat (row to column)

NVL (Column_name,default) NULL function returns default when COLUMN_NAME is empty

3. GROUP BY

(1) Check the Department of goods are wages:

Select Deptno,avg (SAL) from the EMP group by DEPTNO;

Select AVG (SAL) from the EMP group by DEPTNO;

In grouping queries group BY, columns that are not included in the group function are unloaded after the GROUP BY clause, and the columns that are included with group by do not have to be included in the select list.

(2) According to the Department of different positions to the statisticians work goods are wages:

Select Deptno,job,avg (SAL) from the EMP Group by Deptno,job ORDER by Deptno;

Group BY columns are grouped in order, that is, the first column is first installed and then grouped in the second column and so on.

4. HAVING clause

For departments with a salary greater than 2000 of the department's products:

Select Deptno,avg (SAL) from EMP GROUP BY DEPTNO has avg (SAL) > 2000;

The difference between where and having:

1. You cannot use the group functions, such as AVG, Sum, in the WHERE clause, and can be used in the HAVING clause.

2, where is the first filter after the group, having the first group after filtering.

(from the SQL optimization point of view: if where and having both can be used, the where is preferred)

5. Group BY enhancement

(1) Select Deptno,job,sum (SAL) from the EMP group by Deptno,job;

(2) Select Deptno,sum (SAL) from the EMP group by DEPTNO;

(3) Select sum (SAL) from EMP;

(4) Select Deptno,job,sum (SAL) from the EMP Group by Rollup (Deptno,job);

(1) + (2) + (3) = (4)

Oracle Learning 3