In SQL, aggregate functions are built-in functions used to perform calculations on a set of rows and return a single summarized value. These functions are widely used in reporting, analytics, data warehousing, and business intelligence applications.

Oracle SQL supports a rich set of aggregate functions that help in summarizing data across multiple rows. In this tutorial, we’ll explore all standard and Oracle-specific aggregate functions in detail.

---

1. What Are Aggregate Functions?

Aggregate functions perform group-level operations. Instead of operating on individual rows, they operate on groups of rows and return a single result per group.

They are commonly used with the GROUP BY clause, though they can also be used without it (to aggregate over the entire table).

---

2. Standard Aggregate Functions (ANSI SQL – Supported by Oracle)

Oracle supports all ANSI standard aggregate functions:

1. COUNT()

• Returns the number of rows.
• Can count all rows or only non-NULL values.

SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;  -- Ignores NULLs

---

2. SUM()

• Returns the sum of numeric values in a group.
• Ignores NULL values.

SELECT SUM(salary) FROM employees WHERE department_id = 10;

---

3. AVG()

• Returns the average (mean) of numeric values.
• Ignores NULLs.

SELECT AVG(salary) FROM employees;

---

4. MIN()

• Returns the minimum value in the group.
• Works with numbers, strings (alphabetically), and dates.

SELECT MIN(hire_date) FROM employees;

---

5. MAX()

• Returns the maximum value in the group.
• Works with numbers, strings, and dates.

SELECT MAX(salary) FROM employees;

---

3. Oracle-Specific Aggregate Functions

Oracle extends the standard set with several additional aggregate functions:

6. MEDIAN()

• Returns the median (middle) value in a numeric dataset.

SELECT MEDIAN(salary) FROM employees;

---

7. VARIANCE()

• Returns the statistical variance of numeric values.

SELECT VARIANCE(salary) FROM employees;

---

8. STDDEV()

• Returns the standard deviation of numeric values.

SELECT STDDEV(salary) FROM employees;

---

9. LISTAGG() (Oracle 11g and above)

• Concatenates values from multiple rows into a single string, separated by a delimiter.

SELECT LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name)
FROM employees
WHERE department_id = 10;

Example Output: Adams, Bell, Clark, Smith

---

10. CORR(), COVAR_POP(), COVAR_SAMP()

• CORR(x, y): Correlation coefficient between two numeric columns.
• COVAR_POP(x, y): Population covariance.
• COVAR_SAMP(x, y): Sample covariance.

SELECT CORR(salary, commission_pct) FROM employees;

---

11. REGR_ Family (Regression Functions)

Oracle supports a set of linear regression aggregate functions:

• REGR_SLOPE(y, x)
• REGR_INTERCEPT(y, x)
• REGR_COUNT(y, x)
• REGR_R2(y, x) — R-squared value
• REGR_AVGX(x, y), REGR_AVGY(x, y) — means of x and y
• REGR_SXX, REGR_SYY, REGR_SXY — sum of squares

These are useful for statistical modeling and trend analysis.

SELECT REGR_SLOPE(salary, employee_id) FROM employees;

---

12. PERCENTILE_CONT() and PERCENTILE_DISC() (Analytic + Aggregate)

• PERCENTILE_CONT(): Returns a continuous percentile value using interpolation.
• PERCENTILE_DISC(): Returns a discrete percentile value (actual value from the dataset).

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

---

13. COLLECT()

• Returns a nested collection (VARRAY or nested table) of values.

SELECT COLLECT(last_name) FROM employees;

Note: Useful in PL/SQL or when working with object types.

---

14. JSON_ARRAYAGG() and JSON_OBJECTAGG() (Oracle 12c+)

• Used to aggregate values into JSON arrays or objects.

SELECT JSON_ARRAYAGG(last_name) FROM employees;
SELECT JSON_OBJECTAGG(employee_id VALUE last_name) FROM employees;

---

4. Total Number of Aggregate Functions in Oracle SQL

As of Oracle 19c+, the number of aggregate functions supported is over 20, including standard and advanced statistical ones.

Broad Categories:

Category	Examples
Standard Aggregates	COUNT, SUM, AVG, MIN, MAX
Statistical Aggregates	MEDIAN, VARIANCE, STDDEV, CORR
List Aggregates	LISTAGG, COLLECT, JSON_ARRAYAGG
Regression Aggregates	REGR_SLOPE, REGR_INTERCEPT, etc.
Percentile Aggregates	PERCENTILE_CONT, PERCENTILE_DISC
JSON Aggregates	JSON_OBJECTAGG, JSON_ARRAYAGG

---

5. Usage with GROUP BY

Example: Total salary per department

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

---

6. Usage with HAVING

Filter on aggregated values:

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

---

7. NULL Handling

• Most aggregate functions ignore NULLs (except COUNT(*) which counts all rows).
• Be cautious when interpreting averages, sums, etc.

---

8. Performance Consideration

• Some functions like LISTAGG, PERCENTILE_CONT, MEDIAN, or REGR_* may have performance overhead on large datasets.
• Use indexes, materialized views, or analytic functions for performance tuning.