SQL Aggregate Functions

By the end of this lesson, you will be able to summarize large datasets using SQL aggregate functions and group results by specific categories.

What it is

Aggregate functions perform a calculation on a set of values and return a single value. In data science, they are essential for descriptive statistics, such as finding the average revenue per region or counting unique users. The most common aggregates are COUNT(), SUM(), AVG(), MIN(), and MAX().

The mental model is "collapse": imagine taking many rows of raw transaction data and collapsing them into one summary row per category. This is achieved using the GROUP BY clause, which defines how rows are bucketed before aggregation occurs.

Why it matters

• Data Reduction: Transforms millions of rows into manageable summaries for reporting.
• Pattern Recognition: Reveals trends (e.g., sales spikes) that are invisible in raw data.
• Feature Engineering: Creates new variables for machine learning models, such as "average purchase amount per customer."
• Performance Optimization: Aggregating in the database reduces the amount of data transferred to your analysis environment (like Python or R).

Syntax or steps

The basic structure requires selecting the grouping column(s), applying an aggregate function to a numeric column, and specifying the GROUP BY clause with the same non-aggregated columns used in the SELECT statement.

SELECT 
    category_column, 
    AGGREGATE_FUNCTION(numeric_column) AS alias_name
FROM 
    table_name
GROUP BY 
    category_column;

Example

Suppose we have a table named sales with columns region (text) and amount (numeric). We want to find the total sales and average sale size for each region.

SELECT 
    region,
    COUNT(*) AS number_of_transactions,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_transaction_value,
    MIN(amount) AS smallest_sale,
    MAX(amount) AS largest_sale
FROM 
    sales
GROUP BY 
    region
ORDER BY 
    total_revenue DESC;

Explanation:

• SELECT region...: Lists the grouping key and the calculated metrics.
• COUNT(*): Counts all rows in each group, regardless of nulls.
• SUM(amount): Adds up all values in the amount column for that region.
• GROUP BY region: Tells the database to create separate buckets for each unique region name.
• ORDER BY total_revenue DESC: Sorts the final summary so the highest-earning regions appear first.

Common mistakes

• Selecting non-grouped columns: If you select a column not in GROUP BY and not inside an aggregate function, the query will fail (or return unpredictable results depending on the SQL dialect). Always ensure every selected column is either aggregated or grouped.
• Confusing COUNT(*) and COUNT(column): COUNT(*) counts all rows, including those with NULLs. COUNT(column) only counts non-null values in that specific column.
• Filtering after aggregation incorrectly: Using WHERE filters rows *before* grouping. To filter groups based on an aggregate result (e.g., "regions with over $1M revenue"), you must use HAVING.
• Ignoring Data Types: Applying SUM or AVG to text columns will cause errors. Ensure the target column contains numeric data.

When to use it

Use aggregate functions when you need summary statistics. Use window functions (like RANK() or ROW_NUMBER()) when you need calculations across related rows without collapsing the dataset.

Practice

Guided Exercise: Write a query to find the number of distinct customers who made purchases in each month from a table orders with columns customer_id and order_date. Hint: Use COUNT(DISTINCT ...) and extract the month from the date.

Challenge: Modify the previous query to only show months where more than 50 distinct customers placed orders. Hint: Use the HAVING clause.

Quick check

Question: Why can't you use WHERE total_sales > 1000 if total_sales is defined as SUM(amount) in the SELECT clause?

Answer: Because WHERE executes before aggregation. The value total_sales does not exist yet during the filtering phase. You must use HAVING SUM(amount) > 1000 instead.

Summary

SQL aggregate functions combined with GROUP BY allow you to transform detailed transactional data into high-level insights efficiently. Mastering the distinction between WHERE (row filtering) and HAVING (group filtering) is critical for accurate data summarization.