Aggregate Functions

Imagine you have a big pile of numbers, like all the test scores from a class. Instead of looking at each score, you might want to find out some quick facts about the whole class. Aggregate functions are like special calculators in SQL that take a whole bunch of values from many rows and squish them down into one single result. They give you a summary. When you use an aggregate function by itself (without something called GROUP BY, which we'll learn soon), it will calculate its result using all the rows that your FROM and WHERE clauses found.

What it does: This function counts things. It tells you "how many" of something there are.
Different ways to use it:
- COUNT(*): This is like asking, "How many rows are there in total?" It counts every single row, even if some columns in those rows are empty (NULL).

Example: If you have a Students table, and you want to know the total number of students:

SELECT COUNT(*)
FROM Students;

If your Students table has 100 rows, this will give you 100.
- COUNT(column_name): This is like asking, "How many rows have a non-empty value in this specific column?" It only counts rows where the column_name has an actual value. If it's NULL (meaning unknown or empty), that row is not counted for this specific column.

Example: If some students haven't provided an email address (their Email column is NULL), and you only want to count students with an email:

SELECT COUNT(Email)
FROM Students;

If 95 students have an email and 5 don't, this will give you 95.
- COUNT(DISTINCT column_name): This is like asking, "How many unique non-empty values are there in this specific column?" It counts each different value only once.

Example: If your Students table has students from MajorDeptIDs 10, 20, 10, 30, 20, 10, and you want to know how many different departments students are majoring in:

SELECT COUNT(DISTINCT MajorDeptID)
FROM Students;

This will give you 3 (for DeptID 10, 20, and 30, assuming no NULLs).

What it does: Adds up all the numbers in a specified column.
Important: This only works for columns that contain numbers (like INTEGER, DECIMAL, FLOAT).

Syntax: SUM(numeric_column_name)

Example: To find the total amount of money paid in salaries to all employees:

SELECT SUM(Salary)
FROM Employees;

If salaries are 50000, 60000, 70000, this will give you 180000.

What it does: Calculates the average value (the sum of all numbers divided by the count of those numbers) in a specified column.
Important: This also only works for columns that contain numbers.

Syntax: AVG(numeric_column_name)

Example: To find the average grade point average (GPA) across all students:

SELECT AVG(GPA)
FROM Students;

If GPAs are 3.0, 3.5, 4.0, this will give you 3.5.

What it does: Finds the smallest value in a specified column.
Works for: Numbers, text (alphabetical order), and dates/times.

Syntax: MIN(column_name)

Example: To find the earliest birth date among all students (which helps you find the oldest student):

SELECT MIN(DateOfBirth)
FROM Students;

If dates are 2004-01-01, 2003-05-10, 2005-09-15, this will give you 2003-05-10.

What it does: Finds the largest value in a specified column.
Works for: Numbers, text (alphabetical order), and dates/times.

Syntax: MAX(column_name)

Example: To find the highest salary paid to any employee:

SELECT MAX(Salary)
FROM Employees;

If salaries are 50000, 60000, 70000, this will give you 70000.

They always take many rows as input and give you just one summary row as output, unless you use GROUP BY.