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

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;

Result:

total_products
5

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:

total_stock
200

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:

average_price
1960

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:

highest_price	lowest_price
5000	300

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:

category	total_products
Electronics	3
Fitness	2

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:

category	total_stock
Electronics	50
Fitness	150

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:

category	total_products
Electronics	3
Fitness	2

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:

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!

​Recap of the Products Table

​What Are Aggregate Functions?

​COUNT: How Many Rows?

​SUM: Total Stock

​AVG: Average Price

​MAX and MIN: Highest and Lowest Prices

​GROUP BY: Aggregations by Category

​Combining GROUP BY with Other Functions

​Filtering Groups with HAVING

​A Complex Query: Putting It All Together

​Summary

Recap of the Products Table

What Are Aggregate Functions?

COUNT: How Many Rows?

SUM: Total Stock

AVG: Average Price

MAX and MIN: Highest and Lowest Prices

GROUP BY: Aggregations by Category

Combining GROUP BY with Other Functions

Filtering Groups with HAVING

A Complex Query: Putting It All Together

Summary