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_idproduct_namecategorypricestock
1Wireless MouseElectronics120025
2Yoga MatFitness80050
3Bluetooth SpeakerElectronics250015
4Water BottleFitness300100
5SmartwatchElectronics500010

​
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_pricelowest_price
5000300
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:
categorytotal_products
Electronics3
Fitness2
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:
categorytotal_stock
Electronics50
Fitness150
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:
categorytotal_products
Electronics3
Fitness2
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:
categorytotal_stockaverage_pricetotal_products
Electronics5029003
Fitness1505502

​
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!