In this lesson, you will learn how to move beyond looking at individual rows to summarizing entire datasets using counts, sums, and averages.
From Rows to Summaries
Up until now, our SELECT statements have looked at rows one by one. If you had a table of sales, you could see every individual transaction. But what if you want to know the total revenue for the whole month? Or the average price of your products?
Aggregations allow us to "collapse" multiple rows into a single value. SQL provides specific functions to perform these calculations:
COUNT(*): Counts the total number of rows.SUM(column): Adds up all values in a numeric column.AVG(column): Calculates the mathematical average of a column.
Grouping Data Together
Calculating a total for the entire table is helpful, but it’s often more useful to see summaries broken down by categories. For this, we use the GROUP BY clause.
Imagine a products table with columns category and price. If we want to find the average price for each category, we group the rows by the category attribute:
SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;
When you include GROUP BY, SQL takes all the rows that share the same value in the category column, puts them into a "bucket," and then performs the AVG calculation on each bucket individually. Instead of one single result for the whole table, you get one summary row for every unique category.
Why It Matters
Aggregation transforms raw, overwhelming data into actionable insights. By using GROUP BY, you can compare different segments of your data—like comparing sales figures across different regions or counting customers by their sign-up month. Just remember: whenever you use a non-aggregated column (like category) in your SELECT list, it must also appear in your GROUP BY clause. This ensures SQL knows exactly how you want to bucket your data before it calculates the summaries.
Key Takeaway: Aggregation allows you to condense multiple rows into a single summary statistic, and using GROUP BY lets you calculate those statistics for specific subsets of your data.