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

⭐ 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:



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


How this query works:

  • SELECT SUM(Price): Adds together all the numeric values in the Price column.

  • AS TotalSales: Gives the calculated total a column name of TotalSales.

  • FROM Orders: Retrieves the Price values from the Orders table.


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;


How this query works:

  • SELECT Category: Displays the name of each product category.

  • SUM(Price): Adds together the prices of all products within each category.

  • AS TotalSales: Names the calculated total TotalSales.

  • FROM Products: Retrieves the product records from the Products table.

  • GROUP BY Category: Groups products with the same category together so SQL Server calculates a separate total for each category.


This query shows the total sales for each product category.



2. COUNT() — Count Rows



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



SELECT COUNT(*) AS TotalOrders
FROM Orders;


How this query works:

  • SELECT COUNT(*): Counts every row in the Orders table.

  • AS TotalOrders: Gives the calculated count a column name of TotalOrders.

  • FROM Orders: Specifies the table containing the records to count.


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



Counting Unique Values



SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Orders;


How this query works:

  • COUNT(): Counts the number of qualifying values in the CustomerID column.

  • DISTINCT CustomerID: Eliminates duplicate CustomerID values before counting them.

  • AS UniqueCustomers: Names the calculated result UniqueCustomers.

  • FROM Orders: Retrieves customer IDs from the Orders table.


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 Value



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



SELECT AVG(Price) AS AveragePrice
FROM Products;


How this query works:

  • SELECT AVG(Price): Calculates the average of all non-NULL values in the Price column.

  • AS AveragePrice: Names the calculated result AveragePrice.

  • FROM Products: Retrieves the product prices from the Products table.


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 Groups



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


How this query works:

  • SELECT Category: Displays each product category.

  • AVG(Price): Calculates the average price of products within each category.

  • AS AvgPrice: Names the calculated average AvgPrice.

  • FROM Products: Retrieves product information from the Products table.

  • GROUP BY Category: Separates products into groups based on their category and calculates an individual average for each group.


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;


How this query works:

  • SELECT MIN(Price): Finds the smallest numeric value in the Price column.

  • AS CheapestProduct: Names the calculated result CheapestProduct.

  • FROM Products: Searches the Products table for the lowest price.


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:

  • SELECT Category: Displays the name of each product category.

  • MIN(Price): Finds the lowest product price within each category.

  • AS CheapestInCategory: Names the calculated result CheapestInCategory.

  • FROM Products: Retrieves product records from the Products table.

  • GROUP BY Category: Groups products by category so SQL Server finds a separate minimum price for each group.


Returns the lowest product price for each category.



5. MAX() — Maximum Value



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



SELECT MAX(Price) AS MostExpensiveProduct
FROM Products;


How this query works:

  • SELECT MAX(Price): Finds the largest numeric value in the Price column.

  • AS MostExpensiveProduct: Names the calculated result MostExpensiveProduct.

  • FROM Products: Searches the Products table for the highest price.


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:

  • SELECT Category: Displays the name of each product category.

  • MAX(Price): Finds the highest product price within each category.

  • AS MostExpensiveInCategory: Names the calculated result MostExpensiveInCategory.

  • FROM Products: Retrieves product records from the Products table.

  • GROUP BY Category: Groups products by category so SQL Server calculates a separate maximum price for each group.


Returns the highest product price 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;


How this query works:

  • SELECT Category: Displays the name of each product category.

  • COUNT(*) AS ProductCount: Counts the number of products in each category.

  • SUM(Price) AS TotalSales: Adds together all product prices within each category.

  • AVG(Price) AS AvgPrice: Calculates the average product price within each category.

  • MIN(Price) AS Cheapest: Finds the lowest product price in each category.

  • MAX(Price) AS MostExpensive: Finds the highest product price in each category.

  • FROM Products: Retrieves the product records used in the calculations.

  • GROUP BY Category: Groups products by category so all five calculations are performed separately for each category.


Example: Imagine a category contains four products priced at $10, $20, $30, and $40. The query calculates the following:

  • ProductCount: 4
  • TotalSales: $100
  • AvgPrice: $25
  • Cheapest: $10
  • MostExpensive: $40


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


How this query works:

  • SELECT Category: Displays the name of each product category.

  • SUM(Price) AS TotalSales: Calculates the total of the product prices within each category.

  • FROM Products: Retrieves product records from the Products table.

  • GROUP BY Category: Groups products together based on their category.

  • HAVING SUM(Price) > 1000: Filters the grouped results and returns only categories where the combined product prices exceed 1,000.


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
SQL Aggregate Functions 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 VIDEO

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.



Continue Learning SQL