|
⭐ Want to learn SQL step-by-step? Check out our completely FREE Hands-On Practice 👉 SQL course for beginners . SQL aggregate functions allow you to perform calculations on multiple rows of a table and return a single summary value. These functions are essential for data analysis, reporting, and decision-making. The most commonly used aggregate functions are SUM, COUNT, AVG, MIN, and MAX. What Are Aggregate Functions?📽 Watch the Video Tutorial | See Aggregate Functions in Action! Aggregate functions process a collection of values from a column and return a single value. They are often used with the GROUP BY clause but can also be used on their own for totals or averages. Key Features of Aggregate Functions:
1. SUM() — Total ValuesThe SUM() function calculates the total of all numeric values in a column. SELECT SUM(Price) AS TotalSales FROM Orders; How this query works:
This query returns the total sales across all orders. SUM() is commonly used for revenue, costs, or quantities. Example: Sales by CategorySELECT Category, SUM(Price) AS TotalSales FROM Products GROUP BY Category; How this query works:
This query shows the total sales for each product category. 2. COUNT() — Count RowsThe COUNT() function counts the number of rows in a table or group. SELECT COUNT(*) AS TotalOrders FROM Orders; How this query works:
Here, COUNT(*) returns the total number of orders in the table. Counting Unique ValuesSELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers FROM Orders; How this query works:
Example: If the Orders table contains CustomerID values of 101, 102, 101, 103, and 102, the query returns 3 because there are only three unique customers. This counts the number of unique customers who placed orders. 📽 Watch the Video Tutorial | See Aggregate Functions in Action! 3. AVG() — Average ValueThe AVG() function calculates the average of numeric values. SELECT AVG(Price) AS AveragePrice FROM Products; How this query works:
Example: If three products have prices of $10, $20, and $30, the average price is $20. This gives the average price of all products. Using AVG() with GroupsSELECT Category, AVG(Price) AS AvgPrice FROM Products GROUP BY Category; How this query works:
Returns the average price for each product category. 4. MIN() — Minimum ValueThe MIN() function returns the smallest value in a column. SELECT MIN(Price) AS CheapestProduct FROM Products; How this query works:
Example: If product prices are $15, $25, $40, and $10, the query returns $10. Finds the lowest product price in your table. Note that MIN() returns the minimum price, not the product name or ID. Group Example:SELECT Category, MIN(Price) AS CheapestInCategory FROM Products GROUP BY Category; How this query works:
Returns the lowest product price for each category. 5. MAX() — Maximum ValueThe MAX() function returns the largest value in a column. SELECT MAX(Price) AS MostExpensiveProduct FROM Products; How this query works:
Example: If product prices are $15, $25, $40, and $10, the query returns $40. Finds the highest product price in your table. Like MIN(), MAX() returns the price itself, not the product name or ID. Group Example:SELECT Category, MAX(Price) AS MostExpensiveInCategory FROM Products GROUP BY Category; How this query works:
Returns the highest product price in each category. Combining Aggregate FunctionsYou can use multiple aggregate functions in the same query:
SELECT Category,
COUNT(*) AS ProductCount,
SUM(Price) AS TotalSales,
AVG(Price) AS AvgPrice,
MIN(Price) AS Cheapest,
MAX(Price) AS MostExpensive
FROM Products
GROUP BY Category;
How this query works:
Example: Imagine a category contains four products priced at $10, $20, $30, and $40. The query calculates the following:
This query gives a complete summary for each category: number of products, total of product prices, average price, cheapest and most expensive product prices. Aggregate Functions with HAVINGUse HAVING to filter groups based on aggregate results: SELECT Category, SUM(Price) AS TotalSales FROM Products GROUP BY Category HAVING SUM(Price) > 1000; How this query works:
Example: If the total product prices for three categories are $1,500, $800, and $2,000, the query returns only the first and third categories. This query returns categories where the total of their product prices exceeds $1,000.
▶ IWantToLearnSQL.com
FREE SQL VIDEO TUTORIAL
Watch: SQL Aggregate Functions Explained
Learn how to implement SQL Aggregate Functions with practical examples. Follow along as we explain the queries step by step. ▶ WATCH THE FREE VIDEOCommon Mistakes to Avoid
Best Practices
Practice ExercisesTry these to strengthen your understanding:
ConclusionSQL aggregate functions are essential tools for analyzing and summarizing data. Mastering SUM, COUNT, AVG, MIN, and MAX will allow you to generate meaningful insights, create reports, and prepare for real-world SQL projects. Combine them with GROUP BY and HAVING for maximum analytical power. Continue Learning SQL |