Shulou(Shulou.com)06/01 Report--

To fully understand the aggregate function of Oracle, we also need to know what happens when the data contains NULL. Sales rewards are available only to salespeople and not to non-salespeople, so the value of the COMMISSION_PCT field for those non-salespeople reflected in the HR.EMPLOYEES table is NULL. If you want to calculate the average of or sibling COMMISSION_PCT, or the number of rows with values in this field, you can list the results of the aggregate calculation with the following SQL statement:

Select count (commission_pct), avg (commission_pct) from hr.employee

COUNT (COMMISSION_PCT) AVG (COMMISSION_PCT)

38. 255

Although we know that there are 107 employees when counting salaries, when counting sales rewards, the COUNT function ignores those rows with null COMMISSION_CPT and finds that only 38 employees have sales awards. Similarly, when calculating the average sales reward for an employee, Oracle ignores the rows corresponding to the null value and calculates only those rows that have a value.

Only in two special cases, Oracle takes null values into account in the aggregate function. The first is that in the GROUPING function, it is used to test whether the results of the analysis function containing null values are derived directly from the table or from the final aggregate "NULL set" calculated by the analysis. The second case is in the function of COUNT (*). Because the wildcard "*" represents all the fields in the table, Oracle ignores any actual data and counts rows separately, which looks like null values and normal values.

For demonstration purposes, the following SQL statement clearly shows the difference between COUNT (*) and COUNT (COMMISSION_PCT):

Select count (*), count (commission_pct) from hr.employee

-

COUNT (*) COUNT (COMMISSION_PCT)

107 38

-

COUNT (*) counts all the rows in the table, while COUNT (COMMISSION_PCT) counts only those rows in the table where the field COMMISSION_PCT is not NULL.

When grouping for data summarization, the fields or values selected in the SELECT statement either participate in the aggregate calculation or are covered in the GROUP BY clause. If you want to write a grammatically correct GROUP BY clause, always keep in mind that values are either used for grouping or for participating in aggregate calculations-- for no other purpose.

Oracle provides simplified symbols for sort fields when sorting by aggregation or other functions. Instead of cumbersome writing the full names of the fields after the ORDER BY, you can simply use the sequence number of their position in the SELECT statement.

Select department_id, job_id,min (salary), avg (salary), max (salary) from hr.employee group by department_id, job_id order by 1,5 desc

The 1 and 5 after the ORDER BY clause in the statement represent DEPARTMENT_ID and max (salary) in the first and fifth positions in the SELECT statement, respectively.

Based on the results of the aggregate function or grouping, we want to exclude some data sets. That is, we want to follow the GROUP BY clause with a WHERE-like clause to filter after grouping or aggregation.

SQL has a HAVING clause that allows you to select data. The conditions of the HAVING clause can be infinitely complex, so you can use compound conditions in grouping sorting.

Select department_id, job_id, min (salary), avg (salary), max (salary), count (*) from hr.employees group by department_id, job_id having count (*) > 1 and min (salary) between 2500 and 17000 and avg (salary)! = 5000 and max (salary) / min (salary)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.