Aggregate Functions

1. What are Aggregate Functions?

Aggregate functions process a set of values from multiple rows and return one single result.

• Work on groups of rows
• Return a single value
• Used with SELECT statement

2. Types of Aggregate Functions

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

3. COUNT()

The COUNT() function returns the number of rows.

SELECT COUNT(*) FROM student;

SELECT COUNT(Marks) FROM student;

• COUNT(*) counts all rows
• COUNT(column) ignores NULL values

4. SUM()

The SUM() function returns the total of numeric values.

SELECT SUM(Marks) FROM student;

• Works only with numeric columns

5. AVG()

The AVG() function returns the average of numeric values.

SELECT AVG(Marks) FROM student;

• Ignores NULL values

6. MIN()

The MIN() function returns the minimum value.

SELECT MIN(Marks) FROM student;

7. MAX()

The MAX() function returns the maximum value.

SELECT MAX(Marks) FROM student;

8. Aggregate Functions with WHERE

SELECT AVG(Marks)
FROM student
WHERE CourseID = 101;

9. Summary of Aggregate Functions

Function     Purpose
-----------  ------------------------------
COUNT()      Counts rows
SUM()        Adds numeric values
AVG()        Finds average
MIN()        Finds smallest value
MAX()        Finds largest value

Practice Questions

1. What are aggregate functions?
2. Difference between COUNT(*) and COUNT(column).
3. Explain SUM and AVG with example.
4. What does MIN() and MAX() do?
5. Can aggregate functions be used with WHERE?

Practice Task