advanced sql Functions in Oracle 10G phần 5

The Use of Analytical Functions in Reporting (Analytical Functions III) We begin by looking a little closer at the use of GROUP BY. GROUP BY First we look at some preliminaries with respect to the GROUP BY clause. When an aggregate is used in a SQL statement, it refers to a set of rows. The sense of the GROUP BY is to accumulate the aggregate on row-set values. Of course if the aggregate is used by itself there is only table-level grouping, i.e., the group level in the statement “SELECT MAX(hiredate) FROM employee” has the highest group level — that of the table, Employee. The following example illustrates grouping below the table level. Let’s revisit our Employee table: SELECT * FROM employee Which gives: EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY REGION ---------- ------------ --------- ----------- ----------- ------ 101 John 102 Stephanie 104 Christina 108 David 111 Kate 106 Chloe 122 Lindsey 02-DEC-97 35000 39000 W 22-SEP-98 35000 44000 W 08-MAR-98 43000 55000 W 08-JUL-01 37000 39000 E 13-APR-00 45000 49000 E 19-JAN-96 33000 44000 W 22-MAY-97 40000 52000 E 150 Chapter | 5 Take a look at this example of using an aggregate with the GROUP BY clause to count by region: SELECT count(*), region FROM employee GROUP BY region Which gives: COUNT(*) REGION ---------- ------ 3 E 4 W Any row-level variable (i.e., a column name) in the result set must be mentioned in the GROUP BY clause for the query to make sense. In this case, the row-level variable is region. If you tried to run the following query, which does not have region in a GROUP BY clause, you would get an error. SELECT count(*), region FROM employee Would give: SELECT count(*), region * ERROR at line 1: ORA-00937: not a single-group group function The error occurs because the query asks for an aggre-gate (count) and a row-level result (region) at the same time without specifying that grouping is to take place. GROUP BY may be used on a column without the column name appearing in the result set like this: SELECT count(*) FROM employee GROUP BY region 151 The Use of Analytical Functions in Reporting (Analytical Functions III) Which would give: COUNT(*) ---------- 3 4 This latter type query is useful in queries that ask questions like, “in what region do we have the most employees?”: SELECT count(*), region FROM employee GROUP BY region HAVING count(*) = (SELECT max(count(*)) FROM employee GROUP BY region) Gives: COUNT(*) REGION ---------- ------ 4 W Now, suppose we add another column, a yes/no for cer-tification, to our Employee table, calling our new table Employee1. The table looks like this: SELECT * FROM employee1 152 Chapter | 5 Gives: EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY REGION CERTIFIED ------ ------------ --------- ----------- ----------- ------ --------- 101 John 102 Stephanie 104 Christina 108 David 111 Kate 106 Chloe 122 Lindsey 02-DEC-97 35000 22-SEP-98 35000 08-MAR-98 43000 08-JUL-01 37000 13-APR-00 45000 19-JAN-96 33000 22-MAY-97 40000 39000 W Y 44000 W N 55000 W N 39000 E Y 49000 E N 44000 W N 52000 E Y Now suppose we’d like to look at the certification counts in a group: SELECT count(*), certified FROM employee1 GROUP BY certified This would give: COUNT(*) CERTIFIED ---------- --------- 4 N 3 Y As with the region attribute, we have a count of the rows with the different certified values. If nulls are present in the table, then their values will be grouped separately. Suppose we modify the Employee1 table to this: EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY REGION CERTIFIED ------ ------------ --------- ----------- ----------- ------ --------- 101 John 102 Stephanie 104 Christina 108 David 111 Kate 106 Chloe 122 Lindsey 02-DEC-97 35000 22-SEP-98 35000 08-MAR-98 43000 08-JUL-01 37000 13-APR-00 45000 19-JAN-96 33000 22-MAY-97 40000 39000 W Y 44000 W N 55000 W 39000 E Y 49000 E N 44000 W N 52000 E 153 The Use of Analytical Functions in Reporting (Analytical Functions III) The previous query: SELECT count(*), certified FROM employee1 GROUP BY certified Now gives: COUNT(*) CERTIFIED ---------- --------- 3 N 2 Y 2 Note that the nulls are counted as values. The null may be made more explicit with a DECODE statement like this: SELECT count(*), DECODE(certified,null,`Null`,certified) Certified FROM employee1 GROUP BY certified Giving: COUNT(*) CERTIFIED ---------- --------- 3 N 2 Y 2 Null The same result may be had using the more modern CASE statement: SELECT count(*), CASE NVL(certified,`x`) WHEN `x` then `Null` ELSE certified END Certified -- CASE FROM employee1 GROUP BY certified 154 ... - tailieumienphi.vn