Aggregation in SQL

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

​

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;  

Result:

This query counts all rows in the table, giving you the total number of products.

​

SUM: Total Stock

What’s the total stock across all products? Use the SUM function.

SELECT SUM(stock) AS total_stock  
FROM products;  

Result:

This tells you there are 200 units of stock collectively across all products.

​

AVG: Average Price

What’s the average price of your products?

SELECT AVG(price) AS average_price  
FROM products;  

Result:

The average price of your products is ₹1960.

​

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;  

Result:

The most expensive product is ₹5000 (Smartwatch), and the cheapest is ₹300 (Water Bottle).

​

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;  

Result:

This query groups the rows by 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;  

Result:

Here, you’re summing the 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;  

Result:

The HAVING clause works like a filter for aggregated results.

​

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;  

Result:

​

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!