Aggregate functions

Let's assume we have the following Student table

StudentID	Name	Fees	Country

1

Aakash

2000

India

2

Bhavika

2500

USA

3

Chirag

2000

India

4

Divya

3000

UK

5

Eshaan

2500

USA

6

Farhan

NULL

Canada

Aggregate functions are:

used to implement calculation based upon a particular column.These functions always return a single value.

Aggregate functions are:

1. SUM()

This function is used to find the total value of a particular column.

 SELECT SUM (Fees) FROM STUDENT; 

Calculate the Total Fees Paid by All Students:

SELECT SUM(Fees) AS TotalFeesPaid

FROM Student;

Output:

TotalFeesPaid
12000

Explanation: Adds all the non-NULL fees: 2000 + 2500 + 2000 + 3000 + 2500 = 12000.

2. AVG()

This function is used to find the average value of a particular column

SELECT AVG (Fees) FROM STUDENT;

Calculate the Average Fees for Students:

SELECT AVG(Fees) AS AverageFees FROM Student;

Output:

AverageFees
2400.00

Explanation: Average of non-NULL fees: (2000 + 2500 + 2000 + 3000 + 2500) / 5 = 2400.

3. MAX()

This function is used to find the maximum value of a particular column.

SELECT MAX (Fees) FROM STUDENT; 

Find the Maximum and Minimum Fees:

SELECT MAX(Fees) AS MaxFees, MIN(Fees) AS MinFees FROM Student;

Output:

MaxFees	MinFees
3000	2000

Explanation:

• MaxFees: Highest value among fees is 3000.
• MinFees: Lowest non-NULL value is 2000.

4. MIN()

This function is used to find the minimum value of a particular column

 SELECT MIN (Fees) FROM STUDENT; 

5. COUNT() AND COUNT(*)

COUNT()This function is used to find the number of values (i.e. number of rows) of 

a particular column.

SELECT COUNT(Fees) FROM Student;

The COUNT (*) function returns the total number of records in a table, 

counts NULL values also .Count the Total Number of Students

SELECT COUNT(*) AS TotalStudents FROM Student;

Output:

FeesPaidCount	TotalStudents
5	6

Explanation:

• FeesPaidCount: Counts non-NULL values in Fees column = 5.
• TotalStudents: Counts all rows including NULLs = 6.

DISTINCT

DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT Country FROM Customers;

counts and returns the number of different (distinct) countries in the "Customers"

SELECT COUNT(DISTINCT Country) FROM Customers;

Output:

UniqueCountries
4

• Explanation: Distinct countries: India, USA, UK, Canada = 4.