Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
SQL Aggregate
VideosDiscussion
In real life, we rarely look at data row by row. Most of the time, we ask questions like:

  • How many employees are there?
  • What is the total salary paid?
  • What is the average marks of students?
  • What is the highest or lowest value?
These questions are not about individual records, but about summarizing data. To perform such calculations on a group of rows, SQL provides Aggregate Functions.


What Are Aggregate Functions?

Aggregate functions in SQL perform calculations on multiple rows and return a single summarized value.
They help convert large datasets into meaningful insights.

Aggregate functions are commonly used with:

  • SELECT statement
  • GROUP BY clause
  • HAVING clause


Common Aggregate Functions in SQL 

FunctionPurpose
COUNT ( )Counts number of rows
SUM ( )Calculates total value
AVG ( )Calculates average
MIN ( )Finds minimum value
MAX ( )Finds maximum value


Example: employees Table

idnamesalarydepartment
1Amit60000IT
2Neha75000HR
3Rahul50000IT
4Priya90000Finance
5Karan45000HR


1. COUNT( ) Function

Why COUNT( ) Is Needed

Often, we need to know how many records exist in a table or match a condition.

Examples

  • Total number of employees
  • Number of students who passed
  • Number of orders placed


What COUNT( ) Does

COUNT( ) returns the number of rows in a result set.


Syntax

COUNT(column_name)


Example: Count Total Employess

SELECT COUNT(*) AS total_employees
FROM employees;

Returns the total number of rows in the table.


2. SUM( ) Function

Why SUM( ) Is Needed

When working with numeric data, we often need to calculate totals.

Examples

  • Total salary paid by a company
  • Total sales amount
  • Total marks scored


What SUM( ) Does

SUM( ) returns the total of a numeric column.


Syntax

SUM(column_name)


Example: Total Salary of All Employees

SELECT SUM(salary) AS total_salary
FROM employees;


3. AVG( ) Function

Why AVG( ) Is Needed

Average values help in

  • Performance analysis
  • Decision making
  • Comparing data


What AVG( ) Does

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


Syntax

AVG(column_name)


Example: Average Employee Salary

SELECT AVG(salary) AS average_salary
FROM employees;


4. MIN( ) Function

Why MIN( ) Is Needed

To find the lowest value in a dataset.

Examples

  • Lowest salary
  • Minimum marks
  • Cheapest product price


What MIN( ) Does

MIN( ) returns the smallest value from a column.


Example

SELECT MIN(salary) AS lowest_salary
FROM employees;


5. MAX( ) Function

Why MAX( ) Is Needed

To find the highest value in a dataset.

Examples

  • Highest salary
  • Maximum marks
  • Most expensive product


What MAX( ) Does

MAX( ) returns the largest value from a column.


Example

SELECT MAX(salary) AS highest_salary
FROM employees;


Using Aggregate Functions with WHERE Clause

The WHERE clause filters rows before aggregation.


Example: Average Salary of IT Departement

SELECT AVG(salary)
FROM employees
WHERE department = 'IT';


Important Points About Aggregate Functions

  • Aggregate functions return one value
  • They ignore NULL values (except COUNT(*))
  • Mostly used with GROUP BY
  • Cannot be directly used in WHERE clause
  • Useful for reports and analytics


Two Minute Drill

  • Aggreate functions summarize data
  • COUNT ( ) counts rows
  • SUM ( ) adds numeric values
  • AVG ( ) calculates average
  • MIN ( ) finds smallest value
  • MAX ( ) finds largest value
  • Used for reports and analysis