Oracle group functions and group statistics

Group functions and group statistics in Oracle 1. commonly used group functions in SQL include COUNT (): obtain the number of all records -- SELECT COUNT (empno) FROM emp; MAX (): obtain the maximum value in a group -- select max (sal) FROM emp; MIN (): obtain the minimum value -- select min (sal) FROM emp; AVG (): calculate the average value -- select avg (sal) FROM emp; SUM (): SUM -- select sum (sal) FROM emp WHERE deptno = 20; MAX () and MIN () it is generally an application function for numbers. Www.2cto.com 2. GROUP statistics are grouped BY. The SQL syntax format is as follows: [SQL] <span style = "color: #006600; "> SELECT {DISTINCT} * | query column 1 alias 1, query Column 2 alias 2 ,... FROM table name 1 alias 1, table name 2 alias 2 ,... {WHERE condition (s)} {group by grouping condition} {order by sorting field ASC | DESC, sorting field ACS | DESC ,...} </Span> 1. find the number of employees in each department: [SQL] <span style = "color: #006600;"> SELECT deptno, COUNT (empno) FROM emp GROUP BY deptno; -- grouped BY department. </Span> 2. obtain the average salary of each department: [SQL] <span style = "color: #006600;"> SELECT deptno, AVG (sal) FROM emp GROUP BY deptno; </span> Note: 1. if the program uses the grouping function, two conditions are available: the program has group by and the grouping conditions are specified, so that the grouping conditions can be queried together. If grouping is not applicable, the grouping function can only be used separately. 2. When using grouping functions, fields other than grouping functions and grouping conditions cannot appear. 3. group by department and display the Department name. The number of employees in each department is [SQL] <span style = "color: #006600;"> SELECT d. dname, COUNT (e. empno) FROM dept d, emp e WHERE d. deptno = e. deptno group by d. dname; </span> 4. the requirement shows the Department number with an average salary greater than 2000 and the average wage conditions: AVG (sal)> 2000. www.2cto.com [SQL] <span style = "color: #006600;"> SELECT deptno, AVG (sal) FROM emp WHERE AVG (sal)> 2000 GROUP BY deptno; </span> the grouping function can only be used in a group and cannot appear in the WHERE statement. If you want to specify the grouping conditions, you can only use the command of the second condition: HAVING. SQL syntax format: [SQL] <span style = "color: #006600;"> SELECT {DISTINCT} * | query column 1 alias 1, query Column 2 alias 2 ,... FROM table name 1 alias 1, table name 2 alias 2 ,... {WHERE condition (s)} {group by grouping condition {HAVING grouping condition }}{ order by sorting field ASC | DESC, sorting field ACS | DESC ,...} </Span> · use HAVING to complete the preceding operations [SQL] <span style = "color: #006600;"> SELECT deptno, AVG (sal) FROM emp group by deptno having avg (sal)> 2000; </span> 5. displays the non-sales staff's work name and the total monthly salary of the same employee, and the total monthly salary of the employees who want to meet the same job is greater than $5000, the output result is in ascending order of the total monthly salary: 5.1 all non-sales personnel are displayed: job <> 'salesman' [SQL] <span style = "color: #006600; "> SELECT * FROM emp www.2cto.com WHERE job <> 'salesman'; </span> 5.2 calculate the sum of wages by group: [SQL] <span style =" color: #006600; "> SELECT jo B, SUM (sal) FROM emp WHERE job <> 'salesman' GROUP BY job; </span> 5.3 restrict grouping conditions, the total salary is greater than 5000 [SQL] <span style = "color: #006600;"> SELECT job, SUM (sal) FROM emp WHERE job <> 'salesman' group by job having sum (sal)> 5000; </span> 5.4 use sorting, sort in ascending order [SQL] <span style = "color: #006600;"> SELECT job, SUM (sal) su FROM emp www.2cto.com WHERE job <> 'salesman' group by job having sum (sal)> 5000 order by su; </span> simple principle of grouping: · as long as one Only when repeated column content exists can you consider grouping. Note: The grouping function can be nested, but when grouping functions are nested, query statements with grouping conditions cannot appear.