SQL Functions — COUNT, SUM, AVG, MIN, MAX

When working with databases, it’s not always about retrieving individual rows — sometimes you want to get summary statistics, like totals, averages, or the highest value. That’s where aggregate functions come in.

These functions process data across multiple rows and return a single value. Let’s explore the most commonly used ones.

🧠 What Are Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single result .

Function	Description
COUNT()	Counts the number of rows
SUM()	Calculates the total of a column
AVG()	Finds the average value
MIN()	Returns the smallest value
MAX()	Returns the largest value

📋 Sample Table: students

We'll use this table in our examples:

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	91.75
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	Advanced SQL	76.00

🔢 COUNT() — Count the Number of Records

SELECT COUNT ( * ) FROM students;  

Output: 5

You can also count only students in a specific course:

SELECT COUNT ( * ) FROM students WHERE course = 'Advanced SQL' ;  

Output: 4

➕ SUM() — Add Up Values in a Column

SELECT SUM (marks) FROM students;  

Output: 441.25

To sum only the marks of "Advanced SQL" students:

SELECT SUM (marks) FROM students WHERE course = 'Advanced SQL' ;  

Output: 348.75

🧮 AVG() — Average Value

SELECT AVG (marks) FROM students;  

Output: 88.25

Average marks in "Advanced SQL":

SELECT AVG (marks) FROM students WHERE course = 'Advanced SQL' ;  

Output: 87.19

📉 MIN() — Minimum Value

SELECT MIN (marks) FROM students;  

Output: 76.00

📈 MAX() — Maximum Value

SELECT MAX (marks) FROM students;  

Output: 92.50

📊 Grouping with Aggregate Functions

Want to see average marks per course ?

SELECT course, AVG (marks) FROM students GROUP BY course;  

Output:

course	avg_marks
Advanced SQL	87.19
Python	92.50

✅ Real World Use Cases

Scenario	Function Used
Count total users	COUNT()
Get sales total	SUM()
Average rating	AVG()
Lowest price	MIN()
Highest temperature	MAX()

⚠️ Tips

• Always use numeric columns with SUM() and AVG()
• Avoid using GROUP BY unless necessary — it splits rows by category
• Combine with WHERE to filter before calculating

🧾 Summary

• COUNT() – Number of records
• SUM() – Total of a column
• AVG() – Average value
• MIN() – Lowest value
• MAX() – Highest value
• Can be used with WHERE and GROUP BY