SQL Aggregate Functions

Welcome to The Coding College, your destination for mastering coding and programming! In this tutorial, we’ll delve into SQL Aggregate Functions, which allow you to perform calculations on a set of values and return a single summary result.

What Are SQL Aggregate Functions?

SQL Aggregate Functions are built-in functions used to perform computations such as summing values, calculating averages, or finding the maximum value across multiple rows. These functions are commonly used with GROUP BY and HAVING clauses for more advanced operations.

Common SQL Aggregate Functions

Syntax

SELECT aggregate_function(column_name)  
FROM table_name  
WHERE condition  
GROUP BY column_name  
HAVING condition;  

Examples of SQL Aggregate Functions

Sample Table: sales

1. Using AVG(): Calculate Average Price

SELECT AVG(price) AS average_price  
FROM sales;  

Result:

2. Using COUNT(): Count Products in Each Category

SELECT category, COUNT(*) AS product_count  
FROM sales  
GROUP BY category;  

Result:

3. Using MAX(): Find the Most Expensive Product

SELECT MAX(price) AS highest_price  
FROM sales;  

Result:

4. Using MIN(): Find the Least Expensive Product

SELECT MIN(price) AS lowest_price  
FROM sales;  

Result:

5. Using SUM(): Calculate Total Revenue

SELECT SUM(price * quantity) AS total_revenue  
FROM sales;  

Result:

Advanced Use Cases

1. Combining Aggregate Functions

You can calculate multiple aggregate values in a single query.

SELECT  
    COUNT(*) AS total_products,  
    AVG(price) AS average_price,  
    SUM(quantity) AS total_quantity  
FROM sales;  

Result:

2. Using HAVING with Aggregate Functions

The HAVING clause filters grouped results based on aggregate values.

SELECT category, SUM(price * quantity) AS category_revenue  
FROM sales  
GROUP BY category  
HAVING category_revenue > 1000;  

Result:

3. Using Aggregate Functions with DISTINCT

To apply aggregate functions to unique values:

SELECT COUNT(DISTINCT category) AS unique_categories  
FROM sales;  

Result:

Aggregate Functions with NULL Values

Aggregate functions handle NULL values differently:

• COUNT() ignores NULL values.
• SUM(), AVG(), MAX(), MIN() skip NULL values in their calculations.

Example

SELECT  
    COUNT(price) AS count_price,  
    SUM(price) AS sum_price  
FROM sales;  

If a price column has a NULL value, it is ignored in the calculation.

Real-World Applications

• E-Commerce:
  • Calculate total sales revenue for a specific product category.

SELECT category, SUM(price * quantity) AS revenue  
FROM sales  
GROUP BY category;  

• Employee Management:
  • Find the average salary of employees in each department.

SELECT department, AVG(salary) AS average_salary  
FROM employees  
GROUP BY department;  

• Finance:
  • Identify the highest and lowest account balances in a bank.

SELECT MAX(balance) AS highest_balance, MIN(balance) AS lowest_balance  
FROM accounts;  

Best Practices

1. Always Use GROUP BY When Aggregating by Categories:
   Aggregate functions without GROUP BY apply to the entire dataset.
2. Use Aliases for Readability:
   Simplify your output with clear aliases, e.g., AS total_revenue.
3. Handle NULL Values:
   Use functions like COALESCE() to replace NULL values when necessary.
4. Test Queries with SELECT First:
   Preview the data before applying aggregate functions.

Conclusion

SQL Aggregate Functions are essential tools for summarizing and analyzing data. Mastering these functions empowers you to extract valuable insights from your databases efficiently.

For more tutorials and in-depth SQL guides, visit The Coding College and take your SQL skills to the next level!