Multi row function
What is a multiline function?
The grouping function acts on a set of data and returns a value for a set of data.
Null values are ignored by group functions: NVL functions prevent group functions from ignoring null values.
Common multiline functions:
AVG,COUNT,MAX,MIN,SUM 
SQL> --Total wages
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
SQL> --Number
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> --average wage
SQL> select sum(sal)/count(*) One,avg(sal) Two from emp;
//One two
---------- ----------
2073.21429 2073.21429
SQL> --Average bonus
SQL> select sum(comm)/count(*) One,sum(comm)/count(comm) Two,avg(comm) Three
2 from emp;
//One, two, three
---------- ---------- ----------
157.142857 550 550
SQL> select count(*), count(comm) from emp;
COUNT(*) COUNT(COMM)
---------- -----------
14 4
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12month-80 800 20
7499 ALLEN SALESMAN 7698 20-2month -81 1600 300 30
7521 WARD SALESMAN 7698 22-2month -81 1250 500 30
7566 JONES MANAGER 7839 02-4month -81 2975 20
7654 MARTIN SALESMAN 7698 28-9month -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5month -81 2850 30
7782 CLARK MANAGER 7839 09-6month -81 2450 10
7788 SCOTT ANALYST 7566 19-4month -87 3000 20
7839 KING PRESIDENT 17-11month-81 5000 10
7844 TURNER SALESMAN 7698 08-9month -81 1500 0 30
7876 ADAMS CLERK 7788 23-5month -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12month-81 950 30
7902 FORD ANALYST 7566 03-12month-81 3000 20
7934 MILLER CLERK 7782 23-1month -82 1300 10
//Have chosen 14 That's ok.
SQL> --null value 5. The group function will automatically filter the empty space;
SQL> select count(*), count(nvl(comm,0)) from emp;
COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14
SQL> --null value 5. The group function will automatically filter the air; you can nest the filter function to shield its filter functionGrouped data
You can use the group by clause to divide data in a table into groups
SQL > -- average salary of each department
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL > -- grouping of multiple columns
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 order by 1;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
9 rows selected.
SQL > -- grouping of multiple columns: first group by the first column, if the same, then group by the second column, and so on 
SQL> --Query average wage greater than2000Department
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) > 2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
SQL> --whereand having The difference is:whereCannot use multiline function
SQL> --query10Average salary of department No
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having deptno=10;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SQL> ed
//file afiedt.buf written
1 select deptno,avg(sal)
2 from emp
3 where deptno=10
4* group by deptno
SQL> /
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SQL> --SQL optimization 3. Use as much as possiblewhere
//Because having groups first and then filters,whereFilter in groups first.
SQL> host clsgroup by enhancements
SQL> /*
SQL> group by Enhancement
SQL> select deptno,job,sum(sal) from emp group by deptno,job
SQL> +
SQL> select deptno,sum(sal) from emp group by deptno
SQL> +
SQL> select sum(sal) from emp
SQL>
SQL> ====
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job)
SQL>
SQL> abstract
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> No,group by
SQL> */
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
//Have chosen 13 That's ok.
SQL> break on deptno skip 2
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
//Have chosen 13 That's ok.
SQL> break on null
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
//Have chosen 13 That's ok.
SQL> spool off