Aggregate Functions

Definition: Aggregate functions perform a calculation on a set of values and return a single, summarizing value. Instead of looking at individual rows, these functions look at the "big picture" of your data.

Learning Outcome: Mastering aggregate functions like SUM, AVG, and COUNT is a core requirement for data analysis. These tools allow you to transform raw data into meaningful reports, such as calculating total sales, finding average test scores, or counting the number of registered users.

Common Aggregate Functions

SQL provides several built-in functions to handle numerical and descriptive statistics:

Examples: Basic Calculations

To count how many total students are currently in your table:

SELECT COUNT(*) 
FROM students;

To calculate the average age of all students in the database:

SELECT AVG(age) 
FROM students;

Key Notes

• Null Values: Most aggregate functions (except COUNT(*)) ignore NULL values in their calculations. For example, AVG will only average rows that actually contain a number.
• Single Value Output: Remember that a query using an aggregate function without a GROUP BY clause will always return exactly one row as the result.
• Aliasing: It is common practice to use AS to give your result a cleaner name, such as SELECT AVG(age) AS average_student_age.
• Data Types: Functions like SUM and AVG only work on numeric data types (INT, DECIMAL, etc.), while COUNT, MIN, and MAX can work on text and dates as well.