SQL Aggregate Functions — SUM, COUNT, AVG, MIN, MAX Explained

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.



⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!



What Are Aggregate Functions?


📽 Watch Video!

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:



  • Perform calculations across multiple rows


  • Often used in conjunction with GROUP BY


  • Return a single summary value


  • Essential for reports, dashboards, and business analysis


1. SUM() — Total Values



The SUM() function calculates the total of all numeric values in a column.



SELECT SUM(Price) AS TotalSales
FROM Orders;


This query returns the total sales across all orders. SUM() is commonly used for revenue, costs, or quantities.



Example: Sales by Category



SELECT Category, SUM(Price) AS TotalSales
FROM Products
GROUP BY Category;




2. COUNT() — Count Rows



The COUNT() function counts the number of rows in a table or group.



SELECT COUNT(*) AS TotalOrders
FROM Orders;


Here, COUNT(*) returns the total number of orders in the table.



Counting Unique Values



SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Orders;


This counts the number of unique customers who placed orders.



3. AVG() — Average Value



The AVG() function calculates the average of numeric values.



SELECT AVG(Price) AS AveragePrice
FROM Products;


This gives the average price of all products.



Using AVG() with Groups



SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category;


Returns the average price for each product category.



4. MIN() — Minimum Value



The MIN() function returns the smallest value in a column.



SELECT MIN(Price) AS CheapestProduct
FROM Products;


Finds the cheapest product in your table.



Group Example:



SELECT Category, MIN(Price) AS CheapestInCategory
FROM Products
GROUP BY Category;


Returns the cheapest product for each category.



5. MAX() — Maximum Value



The MAX() function returns the largest value in a column.



SELECT MAX(Price) AS MostExpensiveProduct
FROM Products;


Finds the most expensive product in your table.



Group Example:



SELECT Category, MAX(Price) AS MostExpensiveInCategory
FROM Products
GROUP BY Category;


Returns the most expensive product in each category.



Combining Aggregate Functions



You 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;


This query gives a complete summary for each category: number of products, total sales, average price, cheapest and most expensive product.



Aggregate Functions with HAVING



Use HAVING to filter groups based on aggregate results:



SELECT Category, SUM(Price) AS TotalSales
FROM Products
GROUP BY Category
HAVING SUM(Price) > 1000;


This query returns categories where total sales exceed $1000.



Common Mistakes to Avoid



  • Using aggregate functions without GROUP BY when needed


  • Using COUNT(column) vs COUNT(*) incorrectly


  • Confusing WHERE and HAVING — WHERE filters rows before aggregation, HAVING filters after


  • Not aliasing columns (AS TotalSales, AS AvgPrice) — makes results easier to read


Best Practices



  • Always alias your aggregated columns


  • Use GROUP BY to organize results logically


  • Combine multiple aggregate functions to get comprehensive insights


  • Test queries on small datasets before applying to large databases


  • Use DISTINCT with COUNT to avoid double-counting


Practice Exercises



Try these to strengthen your understanding:



  1. Calculate total sales of all products.


  2. Find the average order amount for each customer.


  3. Identify the most expensive and cheapest products in each category.


  4. Count the number of unique customers in your Orders table.


  5. List categories with total sales above $5000.


Conclusion



SQL 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.



⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!