Group/Aggregate
Functions
Multiple rows Functions
• In Oracle SQL, a multiple-row function, more commonly known as an aggregate
function or group function, performs calculations like totals, averages, minimum or
maximum on a set of rows and return a single result for each group or for the
entire table.
• All aggregate functions except COUNT(*) ignore nulls. You can use the NVL or
NVL2 functions in the argument to an aggregate function to substitute a value for
a null.
• Aggregate functions can be used in both the SELECT and HAVING clauses (the
HAVING clause is covered later in this chapter).
• Aggregate functions cannot be used in a WHERE clause. Its violation will
produce the Oracle ORA-00934 group function is not allowed here error
message.
Multiple rows Functions
Most aggregate functions accept the optional DISTINCT and ALL keywords to
control which values are used in the calculation.
DISTINCT: Specifies that the aggregate function should operate on
only unique, non-null values within the group.
ALL: Specifies that the aggregate function should operate on all values
within the group, including duplicates. ALL is the default behavior if neither
keyword is specified.
Common group functions
AVG() Calculates the average value of a numeric column.
COUNT() Returns the number of rows in a set. COUNT(*) counts all rows,
including duplicates and NULL values,
while COUNT(column) counts only non-NULL values in that column
MAX Returns the largest value from the specified column (works with
numeric, character, and date data types).
MIN Returns the smallest value from the specified column (works with
numeric, character, and date data types).
SUM Calculates the total sum of a numeric column.
LISTAGG As an aggregate function, concatenates data from multiple rows
into a single list of values with specified delimiters
Common Group Functions
1. Count()
Counts the number of rows in a table. It can be used in different ways:
COUNT(*): Counts all rows including rows with NULL values.
COUNT(column_name): Counts non-NULL values in the specified column.
COUNT(DISTINCT column_name): Counts unique non-NULL values in the column.
-- Total number of records in the table
SELECT COUNT(*) AS TotalRecords FROM Employees;
-- Count of non-NULL salaries
SELECT COUNT(Salary) AS NonNullSalaries FROM Employees;
-- Count of unique non-NULL salaries
SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM Employees;
NVL,NVL2,COALESCE or DECODE functions can be used to handle the null values
Common Group Functions
2. SUM()
Calculates the total of a numeric column. SUM(column_name) adds up all non-NULL values
in that column
Examples
-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;
-- Calculate the sum of unique salaries
SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM Employee;
NULL values are ignored in both SUM calculations.
NVL,NVL2,COALESCE or DECODE functions can be used to handle the null values
Common Group Functions
3. AVG()
Calculates average value of a numeric column. It divides sum of all non-NULL values by the
number of non-NULL rows
AVG(column_name) returns average of all non-NULL values in the specified column.
Examples
-- Calculates the average of all non-NULL salary values.
SELECT AVG(Salary) AS AverageSalary FROM Employee;
-- Average of distinct non-NULL salary values.
SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;
NULL values are ignored in both AVG calculations.
NVL,NVL2,COALESCE or DECODE functions can be used to handle the null values
Common Group Functions
4. MIN() and MAX()
The MIN() and MAX() functions return the smallest and largest values, respectively,
from a column.
Examples
-- Find the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;
-- Find the lowest salary
SELECT MIN(Salary) AS LowestSalary FROM Employee;
Common Group Functions
• LISTAGG()
The LISTAGG function concatenates strings from multiple rows into a single, delimited string.
LISTAGG (measure_expr [, delimiter_expr])
WITHIN GROUP (ORDER BY sort_expression [ASC | DESC])
measure_expr is the column or expression you want to aggregate.
delimiter_expr is the separator string (e.g., a comma ','). If omitted, it defaults to NULL.
WITHIN GROUP (ORDER BY ...) specifies the order in which the values are concatenated within
each group. This clause is mandatory.
Examples: This query lists all employees, ordered alphabetically by their last name.
SELECT department_id "Dept.",
LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS "Emp_list"
FROM employees;
Summary
• Group Functions are used to manipulate multiple rows and return one
value.
• The NULL values are ignored by default (except for COUNT(*))
• The DISTINCT operator can be used to eliminate duplicate rows and to
work with the unique values
• Multiple group functions can be used in a single query:
GROUPING DATA
GROUP BY statement
GROUP BY clause
• The GROUP BY clause groups a result set into multiple groups and produces a single row of
summary information for each group.
• This clause is commonly used in queries to aggregate data, which means summarizing or
performing calculations on sets of rows.
• For example, the following query displays the average salary of employees for each department:
SELECT department_id, AVG(salary) as avg_sal
FROM employees
GROUP BY department_id;
In this query, you group employees by their department using the GROUP BY clause.
For each group, the query returns the id of the department and the average salary of the employees in
this department .
You use avg() function to calculate the average salary.
GROUP BY clause
• Syntax:
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions ]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY order_expression];
• The GROUP BY clause must include all non-aggregated columns present in
the SELECT list.
• The WHERE clause filters individual rows before grouping.
• The ORDER BY clause sorts the final, grouped result set and is optional.
• The WHERE and ORDER BY clauses are optional
GROUP BY examples
Grouping by multiple columns:
• In this example, we group the employees by department_id and job_id. It will
combine rows with the same department–job pair and calculate the average
salary of employeesthat fall into each group.
SELECT department_id, job_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;
GROUP BY examples
Grouping by expression:
You can use functions within the GROUP BY clause.
The non-aggregate expression in the SELECT list must also appear in the GROUP
BY clause
In this example we use the EXTRACT function to group the data by the YEAR of
the hire_date
SELECT EXTRACT(year FROM hire_date) hire_year, COUNT( *) count
FROM orders
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;
GROUP BY Clause
Using WHERE clause to restrict data
• The following query finds the total salary for each department, but only for
employees who report to manager 103.
• The WHERE clause filters individual employee records before the grouping
occurs.
SELECT department_id "Department Code", SUM(salary) "Total Salary"
FROM employees
WHERE manager_id = 103
GROUP BY department_id;
GROUP BY Clause
• All columns in the SELECT list that are not in group functions must be in the
GROUP BY clause.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
• The GROUP BY column does not have to be in the SELECT list.
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
Wrong Queries
• Any column or expression in the SELECT list that is not an aggregate function must be in the
GROUP BY clause.
SELECT department_id, COUNT(last_name) This query will return an error
FROM employees; ORA-00937: not a single-group group
function
• There are two ways to correct the error:
1-Add the GROUP BY clause. Add the non-aggregated column
SELECT department_id, count(last_name) (department_id in this case) to
FROM employees a GROUP BY clause.
GROUP BY department_id;
If you only want the total salary for all
2-Remove the non-aggregated column employees across all departments, remove
the individual column (department_id) from
SELECT SUM(salary) the SELECT list.
FROM employee; A query with only aggregate functions does
not need a GROUP BY clause
Wrong Queries
• You cannot use the WHERE clause to restrict groups.
• You cannot use group functions in the WHERE clause.
• You use the HAVING clause to restrict groups.
HAVING Clause
• We use the WHERE clause to restrict the rows that you select
• We use the HAVING clause to filter grouped data (i.e. after the data has been
grouped):
1. Rows are grouped.
2. The group function is applied.
3. Groups matching the HAVING clause are displayed.
Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING group_condition;
Using the HAVING Clause
• Example:
To find the maximum salary of each department, but show only the departments
that have a maximum salary of more than $10,000, you need to do the following:
1. Find the maximum salary for each department by grouping by department
id.
2. Then restrict the groups to those departments with a maximum salary
greater than $10,000.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
Using the HAVING Clause
• The WHERE clause and the HAVING clause can be used together in a query
SELECT job_id, SUM(salary) PAYROLL FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
• This example displays the job ID and total monthly salary for each job with a total
payroll exceeding $13,000. (the HAVING clause)
• The WHERE clause excludes sales representatives and sorts the list by the total
monthly salary.
Nested Group Functions
• Group functions can be nested
• The output of the inner function is the input of the outer function
• You have to use group by when nesting group functions
• Group functions can be nested to a depth of two.
• This example displays the maximum average salary.
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
SQL statement – Order of writing
SELECT column_list
FROM table_name
JOIN other_table ON condition
WHERE filter_condition
GROUP BY column_list
HAVING group_filter
ORDER BY column_list
LIMIT number;
SQL statement – Order of Executing
• The database logically processes the query in this order:
• FROM — Identify the source tables.
• JOIN / ON — Combine tables based on join conditions.
• WHERE — Filter rows before grouping.
• GROUP BY — Group the remaining rows.
• HAVING — Filter groups.
• SELECT — Choose the columns or expressions to return.
• DISTINCT — Remove duplicates (if specified).
• ORDER BY — Sort the result set.
• LIMIT / OFFSET — Return only a subset of rows.