RDBMS Aggregate Functions

Aggregate Functions in RDBMS

When working with a database, you often need more than just a list of records. Sometimes you want to know the total number of employees, the average salary, the highest product price, or the total sales for a month.

Instead of calculating these values manually, SQL provides aggregate functions.

Aggregate functions perform calculations on multiple rows of data and return a single result. They help summarize large amounts of information, making it easier to create reports and analyze data.

What are Aggregate Functions?

Aggregate functions are built-in SQL functions that calculate a value using a group of rows. Instead of displaying every record, they return a single summary value.

Some of the most commonly used aggregate functions are:

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

These functions can be used on an entire table or together with the GROUP BY clause to summarize specific categories of data.

COUNT() Function

The COUNT() function returns the total number of rows in a table or the number of rows that match a condition.

SELECT COUNT(*)
FROM Students;

If the Students table contains 100 records, this query returns 100.

SELECT COUNT(*)
FROM Students
WHERE City = 'Delhi';

This query returns the number of students who live in Delhi.

SUM() Function

The SUM() function adds together all the values in a numeric column.

Suppose you have a Sales table.

To calculate the total sales:

SELECT SUM(Amount)
FROM Sales;

The result is 52300. This function is useful for calculating revenue, expenses, salaries, or total quantities.

AVG() Function

The AVG() function calculates the average value of a numeric column.

SELECT AVG(Amount)
FROM Sales;

The database adds all the values and divides them by the number of records.

Businesses often use AVG() to find:

• Average salary
• Average marks
• Average product price
• Average monthly sales

MAX() Function

The MAX() function returns the highest value in a column.

SELECT MAX(Amount)
FROM Sales;

The result is 50000. This tells us that the most expensive product costs Rs. 50,000.

You can also use MAX() to find:

• Highest marks
• Latest order date
• Maximum salary
• Highest temperature

MIN() Function

The MIN() function returns the smallest value in a column.

SELECT MIN(Amount)
FROM Sales;

The result is 800. This tells us that the lowest-priced product costs Rs. 800.

It is commonly used to find:

• Lowest salary
• Minimum marks
• Earliest order date
• Lowest product price

Using Aggregate Functions with GROUP BY

Aggregate functions become even more useful when combined with the GROUP BY clause.

Suppose you have an Employees table.

To calculate the average salary for each department:

SELECT Department,
       AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

Instead of calculating the average manually, SQL summarizes the data automatically.

Best Practices

When using aggregate functions, keep these tips in mind:

• Use aggregate functions only on appropriate columns, especially numeric data.
• Combine them with GROUP BY when you need summaries for different categories.
• Use meaningful aliases with the AS keyword to make results easier to understand.
• Apply the WHERE clause before aggregation when filtering records.
• Choose the function that best matches the information you want to calculate.