Documentation Index
Fetch the complete documentation index at: https://rajanand.org/llms.txt
Use this file to discover all available pages before exploring further.
Now that you’ve mastered the basics of the SELECT statement, it’s time to take things up a notch. Imagine you’re running a growing online store, and you don’t just want to view individual product data—you want insights. For example, how many products do you have? What’s the average price? What’s the total stock?
This is where aggregation comes into play. Aggregations help you summarize and analyze your data efficiently using SQL. Let’s stick to our trusty products table and explore this step by step.
Recap of the Products Table
| product_id | product_name | category | price | stock |
|---|
| 1 | Wireless Mouse | Electronics | 1200 | 25 |
| 2 | Yoga Mat | Fitness | 800 | 50 |
| 3 | Bluetooth Speaker | Electronics | 2500 | 15 |
| 4 | Water Bottle | Fitness | 300 | 100 |
| 5 | Smartwatch | Electronics | 5000 | 10 |
What Are Aggregate Functions?
Aggregate functions perform calculations on a group of rows and return a single result. Common aggregate functions include:
- COUNT(): Counts rows.
- SUM(): Adds values.
- AVG(): Calculates the average.
- MAX(): Finds the largest value.
- MIN(): Finds the smallest value.
Let’s explore these functions one by one.
COUNT: How Many Rows?
Say you want to know how many products are listed in your table.
SELECT COUNT(*) AS total_products
FROM products;
SUM: Total Stock
What’s the total stock across all products? Use the SUM function.
SELECT SUM(stock) AS total_stock
FROM products;
AVG: Average Price
What’s the average price of your products?
SELECT AVG(price) AS average_price
FROM products;
MAX and MIN: Highest and Lowest Prices
Want to find the most and least expensive products? Use MAX and MIN.
SELECT MAX(price) AS highest_price,
MIN(price) AS lowest_price
FROM products;
| highest_price | lowest_price |
|---|
| 5000 | 300 |
GROUP BY: Aggregations by Category
What if you want to summarize your data by category? For example, how many products are there in each category? Enter the GROUP BY clause.
SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;
| category | total_products |
|---|
| Electronics | 3 |
| Fitness | 2 |
category and then counts the number of products in each group.
Combining GROUP BY with Other Functions
Let’s say you want to know the total stock in each category.
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category;
| category | total_stock |
|---|
| Electronics | 50 |
| Fitness | 150 |
stock column for each category.
Filtering Groups with HAVING
The HAVING clause is used to filter groups after they’ve been aggregated. For example, what if you want to see only the categories with more than 1 product?
SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 1;
| category | total_products |
|---|
| Electronics | 3 |
| Fitness | 2 |
A Complex Query: Putting It All Together
Let’s find the total stock, average price, and count of products in each category, but only for categories with more than 1 product.
sqlCopy codeSELECT category,
SUM(stock) AS total_stock,
AVG(price) AS average_price,
COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 1;
| category | total_stock | average_price | total_products |
|---|
| Electronics | 50 | 2900 | 3 |
| Fitness | 150 | 550 | 2 |
Summary
Aggregations are the backbone of data analysis in SQL. By learning functions like COUNT, SUM, AVG, MAX, and MIN, and combining them with GROUP BY and HAVING, you can summarize and filter your data effectively.
Practice these on your own data or stick with the products table for now. Next, we’ll dive into joins, where we connect multiple tables to unlock even richer insights. Keep experimenting, and SQL will become second nature!