Oracle Aggregate Functions Explained

📊 Oracle Aggregate Functions Tutorial

In this tutorial, you'll learn what Oracle aggregate functions are, how they work, and how to use them to calculate summary data from tables. Read the sample Tables Employee for this tutorial.

🔹 What Are Oracle Aggregate Functions?

Oracle aggregate functions operate on a group of rows and return a single value per group. These are widely used in reporting and analytics.

🔸 Using Aggregate Functions with GROUP BY

Use GROUP BY to divide rows into groups. Aggregate functions return one result for each group.

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

Sample Employee Table Structure with SQL code

🔸 Without GROUP BY

Without GROUP BY, aggregate functions calculate results across all rows:

SELECT COUNT(*) FROM employees;

🔸 Using Aggregate Functions in HAVING

HAVING is used to filter results based on aggregate values:

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

🔸 DISTINCT vs. ALL in Aggregate Functions

Some functions accept DISTINCT or ALL to control duplicate handling:

Clause Description
DISTINCT Only unique values are included
ALL (default) Includes all values, including duplicates

Example with DISTINCT:

SELECT AVG(DISTINCT salary) FROM employees;

🔸 NULL Handling in Aggregate Functions

  • Most aggregate functions ignore NULL values.
  • COUNT(*), GROUPING(), and GROUPING_ID() include NULLs.
  • Use NVL() to replace NULL with a default value.

Example:

SELECT SUM(NVL(salary, 0)) FROM employees;

🔸 Oracle Aggregate Functions List

Function Description
AVG() Returns the average value
COUNT() Counts rows or values
LISTAGG() Concatenates values into a single string
MAX() Returns the highest value
MIN() Returns the lowest value
SUM() Returns the total of values

✅ Summary

  • Aggregate functions compute summary results.
  • Use GROUP BY to group data.
  • HAVING filters groups based on aggregate results.
  • Use NVL() to handle NULL values.
  • DISTINCT ignores duplicates; ALL includes them.

Sample Employee Table Structure with SQL code

Now you're ready to use Oracle aggregate functions in your SQL queries!

Keywords:

  • Oracle aggregate functions tutorial
  • Oracle GROUP BY clause training
  • Oracle SUM() function guide
  • Oracle AVG() function tutorial
  • Oracle COUNT() function hands-on
  • Oracle aggregate functions example
  • Oracle SQL aggregate functions course
  • Oracle group functions training
  • Oracle real-time aggregate training
  • Oracle SQL tutorial for beginners
  • Oracle aggregate functions practice

Tags:

#Oracle #SQL #AggregateFunctions #OracleTutorial #SQLTraining #DatabaseLearning #OracleSQL #SQLHandsOn #DataAggregation #SQLForBeginners #OracleGroupBy #SUMFunction #AVGFunction #COUNTFunction

একটি মন্তব্য পোস্ট করুন