Aggregate And Analytical Functions

 

Aggregate functions perform calculations on multiple rows and return a single value as the result.

They are commonly used with the GROUP BY clause to summarize data.

Aggregate functions ignore NULL values (except COUNT(*)).
Can be used with GROUP BY for group-wise calculations.
Cannot be used in the WHERE clause (use HAVING instead)

Common Aggregate Functions :

Function	Description	Example
Count()	Returns total number of rows	Count(*) . count(emp_names)
SUM()	Returns the total sum of a column.	SUM(salary) → Total salary of all employees.
AVG()	Returns the average value.	AVG(salary) → Average employee salary.
COUNT()	Returns the number of rows.	COUNT(*) → Total number of rows in a table.
MAX()	Returns the highest value.	MAX(salary) → Highest salary in the company.
MIN()	Returns the lowest value.	MIN(salary) → Lowest salary in the company.
LISTAGG()	Concatenates values into a single string.	LISTAGG(name, ', ') → Joins names with commas.
MEDIAN()	Returns the median (middle value).	MEDIAN(salary) → Middle salary value.
STDDEV()	Returns the standard deviation.	STDDEV(salary) → Spread of salaries.
VARIANCE()	Returns variance of values.	VARIANCE(salary) → Measures dispersion.

 

 

COUNT(1) counts all rows.

COUNT(*) counts all rows, including NULLs.
COUNT(column_name) ignores NULLs.
COUNT(DISTINCT column_name) counts only unique non-null values.

 

SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary,avg(salary)  AS average_salary FROM employees;

 

LISTAGG():

 

SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employee_names FROM employee;

Result :

Bob, John, Mary

 

 

 

 

Analytical Functions / Windows Function:

 

Function	Use Case
ROW_NUMBER()	Assigns unique row numbers.
RANK()	Assigns ranks, skips numbers for ties.
DENSE_RANK()	Assigns ranks, no skipped numbers for ties.
LEAD()	Fetches next row’s value.
LAG()	Fetches previous row’s value.
SUM() OVER()	Running total within partitions.

 

ROW_NUMBER()

– Assigns a unique number to each row

SELECT name, department, salary,

       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num

FROM employees;

Result:

name	department	salary	row_num
Alice	HR	8000	1
John	HR	5000	2
Tom	IT	9000	1
Bob	IT	7000	2
Mary	IT	6000	3

Numbers are reset within each department (PARTITION BY department).

 

 

RANK()

 Assigns rank but skips numbers for duplicates.

Ranks are skipped when duplicates exist.

SELECT name, salary,

       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank

FROM employees;

DENSE_RANK()

Assigns rank without skipping numbers

SELECT name, department, salary,

       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank

FROM employees;

 Explained with examples -

+--------+--------+------+-----------+

| Name   | Salary | Rank | DenseRank|

+--------+--------+------+-----------+

| John   | 8000   |  1   |     1     |

| Mark   | 6000   |  2   |     2     |

| Sara   | 6000   |  2   |     2     |

| Pam    | 5000   |  4   |     3     |

| Todd   | 4000   |  5   |     4     |

+--------+--------+------+-----------+

LEAD()

– Fetches Next Row’s Value

SELECT name, salary,

       LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary

FROM employees;

LAG()

– Fetches Previous Row’s Value

SELECT name, salary,

       LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary

FROM employees;

 

 

SUM() OVER()

– Running Total (Cumulative Sum)

The SUM() OVER() function calculates a cumulative total over a partition.

SELECT name, salary,

       SUM(salary) OVER (ORDER BY salary DESC) AS running_total

FROM employees;

Result

name	salary	running_total
Tom	9000	9000
Alice	8000	17000
Mary	7000	24000
Bob	7000	31000
John	5000	36000

Explanation:

• The running total adds the salary of each row cumulatively.
• Example:

o   9000 + 8000 = 17000

o   17000 + 7000 = 24000

o   24000 + 7000 = 31000, and so on.

 

 

 

 

 

 

AVG() OVER()

– Moving Average

The AVG() OVER() function calculates a moving average across rows.

SELECT name, salary,

       AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg

FROM employees;

Result

name	salary	moving_avg
Tom	9000	9000
Alice	8000	8500
Mary	7000	7500
Bob	7000	7000
John	5000	6000

✅ Explanation:

• This calculates the average of the current row and the previous row.
• Example calculations:

o   Tom: 9000 (only 1 row)

o   Alice: (9000 + 8000) / 2 = 8500

o   Mary: (8000 + 7000) / 2 = 7500

o   Bob: (7000 + 7000) / 2 = 7000

o   John: (7000 + 5000) / 2 = 6000

 

The NTILE(N)

function in divides the result set into N equal-sized groups (buckets) and assigns a bucket number to each row.

This is useful for percentile rankings, ranking customers/employees into groups, and distributing data evenly.

Buckets may have different sizes if rows are not perfectly divisible.

Oracle itself will decide which record to keep in extra bucket

SELECT name, salary,

       NTILE(3) OVER (ORDER BY salary DESC) AS bucket

FROM employees;

name	salary	bucket
Harry	10000	1
Tom	9000	1
Alice	8000	2
Mary	7000	2
Bob	7000	3
John	5000	3

 

Scenario 2: NTILE(5) with 6 rows

name	salary	bucket
Harry	10000	1
Tom	9000	2
Alice	8000	3
Mary	7000	4
Bob	7000	5
John	5000	5

 General Functions 

Aim	Functions
Conditional Functions	CASE, DECODE, COALESCE
Handle NULL values	NVL, NVL2, COALESCE, NULLIF
Compare multiple values and return the highest/lowest	GREATEST, LEAST
Retrieve system/user-related information	USER, SYSDATE, SYSTIMESTAMP
Convert data types	TO_CHAR, TO_NUMBER, TO_DATE
Perform numeric operations	ROUND, TRUNC, CEIL, FLOOR, MOD