Aggregate Functions

40 min

Aggregate functions perform calculations on sets of values and return single summary values. They enable you to compute statistics, totals, averages, and other summaries from multiple rows. Aggregate functions are essential for reporting and data analysis. Understanding aggregate functions enables you to extract meaningful insights from data. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

`COUNT()` returns the number of rows. `COUNT(*)` counts all rows, including those with NULL values. `COUNT(column)` counts non-NULL values in a column. COUNT is useful for counting records, customers, orders, etc. Understanding COUNT enables you to quantify data. COUNT is often used with GROUP BY to count records per group.

`SUM()` calculates the sum of numeric values in a column. SUM ignores NULL values. SUM is useful for totaling amounts, quantities, scores, etc. Understanding SUM enables you to calculate totals. SUM works with numeric data types.

`AVG()` calculates the average of numeric values. AVG ignores NULL values. AVG is useful for calculating averages, ratings, scores, etc. Understanding AVG enables you to compute means. AVG works with numeric data types.

`MIN()` and `MAX()` return the minimum and maximum values in a column. They work with numeric, date, and string data types. MIN and MAX are useful for finding extremes, earliest/latest dates, etc. Understanding MIN/MAX enables you to find boundaries in your data.

The `GROUP BY` statement groups rows with the same values into summary rows, enabling aggregate functions to operate on each group separately. GROUP BY is essential for aggregating data by categories. Understanding GROUP BY enables you to create summary reports. The `HAVING` clause filters groups after GROUP BY, similar to WHERE but for groups. Understanding HAVING enables you to filter aggregated results. Best practices include using GROUP BY with aggregate functions, using HAVING to filter groups, and understanding that aggregate functions ignore NULL values by default.

Key Concepts

Aggregate functions perform calculations on sets of values.
COUNT, SUM, AVG, MIN, MAX are common aggregate functions.
GROUP BY groups rows for aggregate calculations.
HAVING filters groups after GROUP BY.
Aggregate functions ignore NULL values by default.

Learning Objectives

Master

Using aggregate functions (COUNT, SUM, AVG, MIN, MAX)
Grouping data with GROUP BY
Filtering groups with HAVING
Understanding when to use WHERE vs HAVING

Develop

Data analysis thinking
Understanding how to summarize and aggregate data
Designing effective reporting queries

Tips

Use COUNT(*) to count all rows, COUNT(column) to count non-NULL values.
Use GROUP BY with aggregate functions to create summaries.
Use HAVING to filter groups, WHERE to filter rows before grouping.
Understand that aggregate functions ignore NULL values.

Common Pitfalls

Using aggregate functions without GROUP BY when grouping is needed.
Using WHERE instead of HAVING to filter groups.
Not understanding that aggregate functions ignore NULL values.
Including non-aggregated columns in SELECT without GROUP BY.

Summary

Aggregate functions calculate summaries from multiple rows.
GROUP BY groups rows for aggregate calculations.
HAVING filters groups after aggregation.
Understanding aggregate functions enables data analysis and reporting.
Aggregate functions are essential for extracting insights from data.

Exercise

Write a query to count the number of customers in each country.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Aggregate Functions