Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 4

Aggregate Functions: Counting, Summing, Averaging, and Grouping Data for Insights

A hands-on lesson covering how to summarize rows with COUNT, SUM, AVG, MIN, and MAX, and how to segment those summaries using GROUP BY for meaningful business reporting.

So far you have learned to retrieve data and filter it precisely. But filtering alone does not answer the questions that matter to a business. A manager does not want to read five thousand individual sales rows. They want to know the total revenue, the average order value, the number of transactions, and how those numbers break down by region or by month. That transformation from raw rows to summary numbers is what aggregate functions do. They collapse many rows into one meaningful value. GROUP BY then takes those summaries and organizes them by category, so you can compare performance across segments. This lesson teaches you to calculate totals, averages, and extremes, and to present them grouped by the dimensions that make business sense. These are the building blocks of every report, dashboard, and KPI you will ever build.

1. COUNT: Counting Rows

COUNT is the most commonly used aggregate function. It answers the question how many? But there are three ways to use it, and they do not all give the same answer. Understanding the difference is critical because a wrong COUNT can mislead an entire business decision.

COUNT(*): count every row. This counts all rows in the result set, regardless of what is in any particular column. It is the fastest and most reliable way to get a row count:

SELECT COUNT(*) FROM customers;

This returns a single number: the total number of customers in the table. Even if every column in a row is NULL, COUNT(*) still counts that row, because it counts the row itself, not the contents.

COUNT(column): count non-NULL values. When you name a specific column, COUNT only tallies rows where that column is not NULL:

SELECT COUNT(email) FROM customers;

If the table has one thousand customers but only eight hundred have provided an email address, this returns 800. The two hundred missing emails, stored as NULL, are excluded. This distinction matters when you are calculating completion rates or coverage percentages. If you need the percentage of customers with emails, you would divide COUNT(email) by COUNT(*) and multiply by 100.

COUNT(DISTINCT column): count unique values. This counts how many different values appear in a column, ignoring duplicates and NULLs:

SELECT COUNT(DISTINCT region) FROM customers;

If you have ten thousand customers spread across six regions, this returns 6, not 10000. It is the standard way to answer questions like how many unique product categories do we sell? or how many different payment methods have been used? Without DISTINCT, COUNT(payment_method) would return the total number of transactions, which is not the same as the number of unique methods.

COUNT with WHERE. You can combine COUNT with filtering to answer conditional questions:

SELECT COUNT(*) FROM orders WHERE status = 'delivered' AND order_date >= '2026-01-01';

This returns the number of orders delivered this year. The WHERE clause filters the rows first, and COUNT tallies only the survivors. This is how you build KPIs directly in SQL.

2. SUM: Totalling Values

SUM adds up all the values in a numeric column. It is the function behind every total revenue, total cost, and total profit report. Because it deals with money and quantities, precision matters. This is why you should only use SUM on DECIMAL or INT columns, never on FLOAT or DOUBLE, for the same reason you store money as DECIMAL: to avoid floating-point rounding errors.

Basic SUM syntax. To calculate total revenue across all orders:

SELECT SUM(total_amount) FROM orders;

This returns one number: the sum of every value in the total_amount column. If the table is empty, or if all values are NULL, SUM returns NULL, not zero. If you need zero instead of NULL for display purposes, wrap the function in COALESCE:

SELECT COALESCE(SUM(total_amount), 0) FROM orders;

SUM with expressions. You can sum the result of a calculation, not just a raw column. To calculate total discount given across all orders:

SELECT SUM(total_amount * discount_percent / 100) FROM orders;

This computes the discount for each row individually, then adds all those discounts together. The expression inside SUM is evaluated once per row, and the results are aggregated. This is how you calculate weighted totals, tax amounts, and commissions directly in SQL.

SUM with WHERE. To find total revenue only for a specific category or time period:

SELECT SUM(total_amount) FROM orders WHERE order_date >= '2026-01-01';

This returns the year-to-date revenue. Combining SUM with date filters is how you build monthly, quarterly, and annual financial reports without exporting data to Excel.

3. AVG: Calculating Average

AVG calculates the arithmetic mean of a numeric column: the sum of all values divided by the count of non-NULL values. It is the standard function for average order value, average customer age, average response time, and any other mean-based metric.

Basic AVG syntax. To find the average order value:

SELECT AVG(total_amount) FROM orders;

This returns the mean of the total_amount column. Like SUM, AVG ignores NULL values entirely. If you have five orders with values 10000, 20000, NULL, 30000, and 40000, AVG returns 25000, not 20000. It divides by 4, the count of non-NULL values, not by 5, the total row count. This is usually what you want, but be aware of it when interpreting results on sparse data.

AVG with rounding. AVG often returns many decimal places. For financial reporting, you usually want two decimal places. Use the ROUND function:

SELECT ROUND(AVG(total_amount), 2) FROM orders;

This rounds the average to two decimal places, suitable for currency display. You can also use FORMAT for comma separators, though that returns a string rather than a number, which may be harder to use in further calculations.

Average versus median. AVG is sensitive to extreme values. If you have ninety-nine orders of ten thousand naira and one order of ten million naira, the average is approximately 109,900 naira, which does not represent a typical order at all. In such cases, the median is a better measure of central tendency. MySQL does not have a built-in MEDIAN function, but you can calculate it with window functions or user-defined variables. For now, recognize that AVG tells you the mean, not necessarily the middle. Always examine your minimum and maximum values alongside the average to spot outliers.

AVG with DISTINCT. You can average only unique values, though this is rarely useful for financial data:

SELECT AVG(DISTINCT total_amount) FROM orders;

This removes duplicate total_amount values before calculating the mean. Use it cautiously, because in most business contexts, duplicate values are valid and should be included.

4. MIN and MAX: Finding Extremes

MIN and MAX return the smallest and largest values in a column. They work on numbers, dates, and text. For numbers, they find the lowest and highest values. For dates, they find the earliest and latest. For text, they find the first and last alphabetically according to the collation.

Numeric extremes. To find the smallest and largest order values:

SELECT MIN(total_amount), MAX(total_amount) FROM orders;

This returns two columns in one row: the cheapest order and the most expensive order. These values are useful for spotting outliers, setting axis ranges on charts, and validating data entry. If MAX(total_amount) is ten billion naira for a retail product, you probably have a data quality issue.

Date extremes. MIN and MAX on date columns give you the first and last dates in the dataset:

SELECT MIN(order_date), MAX(order_date) FROM orders;

This tells you the date range of your data. It is a standard first step when you receive a new dataset, because it confirms whether the data covers the expected time period and whether there are future dates or ancient dates that should not be there.

Text extremes. On text columns, MIN returns the alphabetically first value and MAX returns the alphabetically last:

SELECT MIN(last_name), MAX(last_name) FROM customers;

This is less commonly used in reporting, but it can help you verify the alphabetical range of coded values or to spot entries that start with symbols or numbers, which would sort before letters in many collations.

NULL handling. MIN and MAX ignore NULL values, just like SUM and AVG. If a column contains only NULLs, they return NULL. They also work with DISTINCT, though MIN(DISTINCT column) is redundant because the minimum of a set is the same as the minimum of its unique values.

5. GROUP BY: Grouping Results

Aggregate functions become truly useful when you combine them with GROUP BY. Without GROUP BY, every aggregate function collapses the entire table into a single summary row. With GROUP BY, you get one summary row per category, which is what business reporting actually requires. A manager does not want one total revenue number. They want total revenue by region, by month, and by product category.

The GROUP BY syntax. To calculate total revenue per region:

SELECT region, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY region;

MySQL scans the orders table, groups all rows with the same region value together, and then applies SUM(total_amount) to each group separately. The result is one row per region, showing the region name and its total revenue. The column you group by, region, must appear in the SELECT list. The aggregate function, SUM, operates on the rows within each group.

Grouping by multiple columns. You can group by more than one column to create finer segments. To see revenue by region and by month:

SELECT region, MONTH(order_date) AS order_month, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY region, MONTH(order_date);

This returns one row for every unique combination of region and month. If you have six regions and twelve months, you could get up to seventy-two rows. The order of columns in GROUP BY does not affect the result set, but it can affect performance if you have indexes that match the grouping order. For readability, list grouping columns in the same order they appear in the SELECT clause.

The rule for SELECT with GROUP BY. In standard SQL, every column in the SELECT list must either be inside an aggregate function or be listed in the GROUP BY clause. MySQL is more permissive than some databases and may allow non-aggregated columns that are not in GROUP BY, but the result is unpredictable. Build the correct habit now: if a column is in SELECT and it is not wrapped in an aggregate function, it must be in GROUP BY. This ensures your query is portable to PostgreSQL, SQL Server, and Oracle, and it guarantees deterministic results.

Sorting grouped results. GROUP BY does not guarantee any particular output order. To see your highest-revenue regions first, add ORDER BY:

SELECT region, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

DESC means descending, so the largest total appears first. ASC means ascending and is the default if you omit the direction. ORDER BY comes after GROUP BY in the query structure, because sorting happens after the groups have been formed and aggregated.

6. Combining Aggregate Functions with GROUP BY

Real reports rarely use just one aggregate. A typical sales report shows the count of orders, the sum of revenue, the average order value, and the minimum and maximum order, all broken down by category or by sales representative. You can include multiple aggregate functions in a single SELECT, all grouped by the same dimensions.

Multiple aggregates in one query. To build a complete sales summary by region:

SELECT
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value,
  MIN(total_amount) AS min_order,
  MAX(total_amount) AS max_order
FROM orders
GROUP BY region;

This returns one row per region with six columns of insight. You can see at a glance which regions have high volume, which have high value, and which have extreme outliers. This is the exact structure of a regional sales report that a director would expect to see in a weekly email.

Filtering grouped results with HAVING. The WHERE clause filters rows before aggregation. But what if you want to filter after aggregation? For example, you want to see only regions with total revenue above one million naira. You cannot use WHERE SUM(total_amount) > 1000000 because WHERE runs before SUM is calculated. The HAVING clause exists for this exact purpose:

SELECT region, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY region
HAVING SUM(total_amount) > 1000000;

HAVING is evaluated after GROUP BY and after the aggregate functions are computed. It keeps only the groups that meet its condition. Regions with total revenue below one million are discarded from the result. You can combine WHERE and HAVING in the same query: WHERE filters the raw rows, GROUP BY forms the groups, and HAVING filters the groups:

SELECT region, SUM(total_amount) AS total_revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region
HAVING SUM(total_amount) > 1000000;

This returns only regions whose year-to-date revenue exceeds one million. The WHERE clause excludes orders from last year before the groups are even formed. The HAVING clause then excludes regions that do not meet the threshold after summing. This sequence, WHERE then GROUP BY then HAVING then ORDER BY, is the standard logical execution order of a SQL query.

HAVING with multiple conditions. You can use AND and OR in HAVING just like in WHERE:

SELECT region, COUNT(*) AS order_count, AVG(total_amount) AS avg_value
FROM orders
GROUP BY region
HAVING COUNT(*) > 100 AND AVG(total_amount) > 50000;

This returns regions that have both more than one hundred orders and an average order value above fifty thousand naira. It is a quality filter: high volume alone is not enough, and high value alone is not enough. Only regions that satisfy both conditions appear.

A practical reporting pattern. One of the most common aggregate queries in business is the monthly summary:

SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*) AS orders,
  SUM(total_amount) AS revenue,
  ROUND(AVG(total_amount), 2) AS aov
FROM orders
WHERE status = 'delivered'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
HAVING COUNT(*) >= 10
ORDER BY month;

This produces a clean monthly report with order count, revenue, and average order value, including only delivered orders and only months with at least ten orders. The DATE_FORMAT function groups by year and month while preserving chronological order. This single query replaces an entire Pivot Table that would otherwise be built in Excel.

Using AI to Move Faster in SQL Aggregation

Aggregate queries are conceptually simple but syntactically easy to get wrong, especially when combining GROUP BY, HAVING, and multiple functions. AI can help you construct these queries correctly, explain execution logic, and optimize reporting patterns.

1. Translate report requirements directly into SQL.
When a manager asks for a report, they rarely speak in SQL terms. They say: "I need a monthly breakdown of sales by region, showing total revenue, number of orders, and average order size, but only for regions that did more than fifty orders." You can paste this into Copilot and ask: "Write a MySQL query for this report requirement." AI will generate the correct structure with GROUP BY region, MONTH(order_date), multiple aggregates, and a HAVING clause. Your job is to verify the date function matches your MySQL version, confirm the column names match your schema, and test the HAVING threshold against a known region to ensure the logic is inclusive or exclusive as intended.

2. Use AI to explain the difference between WHERE and HAVING.
This is a persistent source of confusion. If you are unsure whether a condition belongs in WHERE or HAVING, ask AI: "I want to filter out cancelled orders and then show only categories with total revenue above one million. Which condition goes in WHERE and which goes in HAVING?" AI will explain that cancelled orders are a row-level filter belonging in WHERE, while the revenue threshold is a group-level filter belonging in HAVING. Understanding this distinction is more important than memorizing syntax, and AI explanations can solidify your mental model faster than trial and error.

3. Generate sample data to test edge cases in aggregation.
Aggregates behave differently with empty tables, NULL-heavy columns, and single-row groups. Ask AI: "Generate a small orders table with edge cases including NULL total_amount, a region with only one order, and a region with no orders, then write queries to test how COUNT, SUM, AVG, MIN, and MAX behave on each." Running these tests yourself builds intuition for why AVG ignores NULLs, why COUNT(*) and COUNT(column) differ, and why MIN on a single-row group returns that row's value. This is the kind of deliberate practice that prevents production surprises.

4. Refactor Excel Pivot Table logic into SQL.
If you are comfortable with Excel Pivot Tables but new to SQL GROUP BY, you can describe your Pivot Table layout to AI and ask for the SQL equivalent. For example: "In Excel I put Region in Rows, Order Date in Columns grouped by month, and Sum of Revenue in Values. What is the MySQL query?" AI will produce a GROUP BY query with DATE_FORMAT for the month extraction and SUM for the values. This bridges your existing Excel knowledge into SQL and accelerates your transition from spreadsheet analyst to database analyst.

5. Verify AI-generated GROUP BY queries for correctness.
AI sometimes generates GROUP BY queries that are syntactically valid but logically wrong. Common errors include grouping by a column that is not in SELECT, forgetting to aggregate a column that should be summed, or using HAVING on a non-aggregated column that should have been filtered in WHERE. Always run the query and manually verify a few groups. Pick one region, calculate its total revenue by hand from the raw data, and compare it to the query result. If they match, the query is probably correct. If they do not, debug the grouping columns and the filter conditions before trusting the full output.

A habit worth building from this lesson onward: whenever you need a grouped report, sketch the desired output columns first, identify which are dimensions, the grouping columns, and which are measures, the aggregated values, then ask AI to write the query, then audit it for correct GROUP BY columns, appropriate HAVING conditions, and accurate ORDER BY sorting. This workflow produces correct reports faster than building queries from scratch and reduces the risk of the subtle logic errors that plague multi-aggregate queries.

Next lesson: joining tables with INNER JOIN, LEFT JOIN, and understanding table relationships.