Group Functions on Oracle SQL

Topic Introduction: In Oracle SQL, group functions (also known as aggregate functions) are used to perform calculations on groups of rows to return a single result for each group. These functions are commonly used with the GROUP BY clause to group rows based on one or more columns and then apply the aggregate function to each group

Types of Group Function

COUNT: This function counts the number of rows in a group.

SUM: This function calculates the sum of values in a column for a group.

AVG: This function calculates the average of values in a column for a group.

MIN: This function returns the minimum value in a column for a group.

MAX: This function returns the maximum value in a column for a group.

VARIANCE: This function is used for calculating the variance of a numeric column within a group.

STDDEV: This function is used for calculating the standard deviation of a numeric column within a group.

Here are also some aggregate functions that call also analytical functions such as LISTAGG(), FIRST_VALUE(), and LAST_VALUE() we will discuss this later.

Group Function Syntex

SELECT [column,] group_function(column)

FROM table

WHERE condition

[GROUP BY column]

[ORDER BY column]

On the select clause, we see [column,] it is not mandatory, If we want to see group data according to one or multiple columns we need to use the column name or expression in this close beside the group function column and in this case we must use column on the Group by clause which column name we have refer to select clause [column,]. We will discuss this GROUP BY clause in the next details. Here we discuss the group on overall data in a table or query.

group_function(column): Here we will use the group function and into the bracket, we will use the column name or expression

Group Function Example and Explanation

SELECT COUNT (*) AS employee_count FROM employees;

SELECT SUM (salary) AS total_salary FROM employees;

SELECT AVG (salary) AS avg_salary FROM employees;

SELECT MIN (salary) AS min_salary FROM employees;

SELECT MAX (salary) AS max_salary FROM employees;

SELECT VARIANCE (salary) AS salary_variance, STDDEV (salary) AS salary_stddev

  FROM employees;

DISTINCT Function with Group Function

Query:

SELECT COUNT (last_name) FROM employees;

SELECT COUNT (DISTINCT last_name) FROM employees;

If we execute 1st query this query will return 107 and If we execute 2nd query this query will return 102. The first query count all name in the employees' table but the second query initially find out the unique name using the DISTINCT function and then count those names so this count only unique name in the table

Group Function and Null Values

All group functions ignore null values in the column. However, the NVL function forces group functions to include null values.

Here is two example 1st one is without handling NULL and 2nd one is Handling NULL using NVL. 1st query average value will be greater than 2nd query if comission_pct null exists because the Group function always ignores the null value. When we handle null value using NVL this comission_pct column gets value against the null value and the average value will decrease.

SELECT COUNT (last_name) FROM employees;

SELECT COUNT (DISTINCT last_name) FROM employees;

These group functions are essential for summarizing and aggregating data in Oracle SQL queries, especially when dealing with large datasets or when you need to obtain summary information for specific groups within your data.