📖 SQL GROUP BY & Aggregate Functions: COUNT, SUM, AVG, MIN, MAX Explained
SQL GROUP BY & Aggregate Functions: When working with large datasets, you often need to summarize and analyze data rather than just retrieve individual records. SQL GROUP BY combined with aggregate functions allows you to do just that.
In this guide, you’ll learn:
✅ What SQL GROUP BY does and when to use it
✅ How to use COUNT, SUM, AVG, MIN, and MAX functions
✅ Practical examples to analyze data effectively
By the end, you’ll be able to group and summarize data like a pro! 🚀
📌 1. What is SQL GROUP BY?
The GROUP BY statement is used to group rows that have the same values in specified columns. It’s commonly used with aggregate functions to perform calculations on grouped data.
Syntax of GROUP BY
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;📌 2. Using COUNT() to Count Records
The COUNT() function counts the number of records in a group.
Example: Count the Number of Customers in Each City
SELECT City, COUNT(*) AS Total_Customers
FROM Customers
GROUP BY City;Result:
| City | Total_Customers |
|---|---|
| New York | 3 |
| Chicago | 2 |
| Miami | 1 |
✅ This query groups customers by city and returns the count of customers in each city.
📌 3. Using SUM() to Calculate Totals
The SUM() function calculates the total sum of a column’s values.
Example: Total Sales per Customer
SELECT CustomerID, SUM(Amount) AS Total_Spent
FROM Orders
GROUP BY CustomerID;Result:
| CustomerID | Total_Spent |
|---|---|
| 1 | 1500 |
| 2 | 1200 |
| 3 | 800 |
✅ This query groups orders by customer and returns the total amount spent by each customer.
🖼️ Image Placeholder: SQL SUM() function example showing total sales per customer
📌 Alt Tag: “SQL SUM() function calculating total sales per customer.”
📌 4. Using AVG() to Find Averages
The AVG() function calculates the average value of a column.
Example: Find the Average Order Amount per City
SELECT City, AVG(Amount) AS Avg_Order_Amount
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY City;Result:
| City | Avg_Order_Amount |
|---|---|
| New York | 750 |
| Chicago | 600 |
| Miami | 800 |
✅ This query groups orders by city and calculates the average order amount per city.
📌 5. Using MIN() and MAX() to Find Extremes
MIN()finds the smallest value in a group.MAX()finds the largest value in a group.
Example: Find the Cheapest and Most Expensive Orders per Customer
SELECT CustomerID, MIN(Amount) AS Min_Order, MAX(Amount) AS Max_Order
FROM Orders
GROUP BY CustomerID;Result:
| CustomerID | Min_Order | Max_Order |
|---|---|---|
| 1 | 500 | 1000 |
| 2 | 300 | 900 |
| 3 | 800 | 800 |
✅ This query groups orders by customer and returns the cheapest and most expensive orders for each customer.
📌 6. Filtering Grouped Data with HAVING
The HAVING clause filters grouped results, similar to WHERE, but works with aggregate functions.
Example: Find Cities with More Than 2 Customers
sqlCopyEditSELECT City, COUNT(*) AS Total_Customers
FROM Customers
GROUP BY City
HAVING COUNT(*) > 2;
Result:
| City | Total_Customers |
|---|---|
| New York | 3 |
✅ This query only returns cities with more than 2 customers.
📌 7. Common Mistakes to Avoid
🚫 Using WHERE Instead of HAVING for Aggregates:
-- ❌ Incorrect
SELECT City, COUNT(*) FROM Customers WHERE COUNT(*) > 2 GROUP BY City;✅ Fix:
SELECT City, COUNT(*) FROM Customers GROUP BY City HAVING COUNT(*) > 2;🚫 Forgetting to Group By Non-Aggregate Columns:
-- ❌ Incorrect
SELECT City, COUNT(*) FROM Customers;✅ Fix:
SELECT City, COUNT(*) FROM Customers GROUP BY City;
📌 Final Thoughts: Why Master GROUP BY & Aggregate Functions?
Mastering GROUP BY and aggregate functions helps in:
✔ Analyzing business data efficiently 📊
✔ Generating summary reports 📋
✔ Filtering grouped results for deeper insights 🔍
Next up in the series: “SQL Subqueries and Nested Queries: How to Write Efficient SQL Queries” – Stay tuned! 🚀