? Group functions: – Type and syntax – Use AVG, SUM, MIN, MAX, count– to use the DISTINCT keyword in group functions – Null values in group functions
The group function evaluates the rowset and provides a result for each group. Unlike a single-line function, group functions are used to evaluate rowsets to provide a result for each group. These collections can contain entire tables, or they can contain groups that are partitioned into tables.
? Avg? COUNT? MAX? MIN? STDDEV? SUM? VARIANCE each function accepts a parameter. The following table lists the options that you can use in the syntax:
SELECT group_function (column), ... From Table[where condition][order by column]; Group function: The syntax group function should be placed after the SELECT keyword. You can use commas to separate multiple groups of functions. Guidelines for using group functions:? Distinct makes the function consider only distinct values; all causes the function to consider each value (including duplicate values). The default value is all, so you don't need to specify.? The data type of a function that uses the expr parameter can be char, VARCHAR2, number, or date. All group functions ignore null values. To replace null values with a value, use the NVL, NVL2, coalesce, case, or decode functions.
- Using the AVG and SUM functions
You can use the AVG and SUM functions with numeric data.
[email protected]> SELECT
AVG (Salary), MAX (Salary), MIN (Salary), SUM (Salary)
From employees WHERE job_id like '%rep% ';
AVG (SALARY) MAX (SALARY) MIN (SALARY) SUM (SALARY)
----------- ----------- ----------- -----------
8272.72727 11500 6000 273000You can use the AVG, SUM, Min, and Max functions for columns that can store numeric data. The example shows the average monthly, highest, lowest, and sum of all sales reps.
using the Min and Max functionsYou can use the Min and Max functions for numeric, character, and date data types.
[Email protected]> SELECT MIN (hire_date), MAX (hire_date) from employees;
MIN (hire_date) MAX (hire_date)
------------------ ------------------
13-jan-01 21-apr-08You can use the Max and Min functions for numeric, character, and date data types. The example shows the employee who has the shortest and longest duration of office. The following example shows the first and last name of the employee in the list that contains all employees in alphabetical order with the last name:
[Email protected]> SELECT MIN (last_name), MAX (last_name) from employees;
MIN (last_name) MAX (last_name)
------------------------- -------------------------
Abel ZlotkeyNote: The AVG, SUM, Variance, and StdDev functions can only be used to process numeric data types. The max and Min functions cannot be used to handle lob or long data types.
COUNT (*) returns the number of rows in the table:
[Email protected]> SELECT COUNT (*) from employees WHERE department_id =;
COUNT (*)
----------
$The example shows the number of employees in department 50. COUNT (expr) returns the number of rows that expr is a non-null value:
[Email protected]> SELECT COUNT (commission_pct) from employees WHERE department_id =;
COUNT (commission_pct)
---------------------
theThe example shows the number of employees in department 80 who can earn a commission. The Count function has the following three formats:? COUNT (*)? COUNT (expr)? Count (DISTINCT expr) count (*) is used to return the number of rows in a table that meet the criteria of the SELECT statement, including duplicate rows and rows that contain null values in any column. If the SELECT statement contains a WHERE clause, COUNT (*) returns the number of rows that meet the criteria in the WHERE clause. Instead, count (expr) returns the number of non-null values in the column identified by expr. Count (DISTINCT expr) returns the number of distinct non-null values in the column identified by expr.
- Using the DISTINCT keyword
? Count (DISTINCT expr) returns the number of different non-null values for expr. To display the number of different department values in the Employees table, you can use:
[Email protected]> SELECT COUNT (DISTINCT department_id) from employees;
COUNT (distinctdepartment_id)
----------------------------
OneUse the DISTINCT keyword to avoid counting any duplicate values in a column. The example shows the number of different department values in the Employees table.
- Group functions and Null values
The group function ignores null values in the column:
[Email protected]> SELECT AVG (commission_pct) from employees;
AVG (commission_pct)
-------------------
. 222857143Averages are calculated based only on rows that have valid values stored in the commission_pct column of the table. The average is calculated by dividing the sum of the commissions paid to all employees by the number of employees who earn commissions (4). The NVL function forces the group function to include a null value:
[Email protected]> SELECT AVG (NVL (commission_pct, 0)) from employees;
AVG (NVL (commission_pct,0))
--------------------------
. 072897196Averages are calculated based on all rows in the table, regardless of whether null values are stored in the commission_pct column. The average is calculated by dividing the sum of commissions paid to all employees by the total number of employees in the company (20). All group functions ignore null values in the column. However, the NVL function forces the group function to include null values.
