SQL Aggregate Functions

Aggregate Functions perform calculations on multiple rows and return a single summarized value. They are commonly used for reporting, analytics, and generating insights from datasets.

📌 Common Aggregate Functions

• COUNT(column) – Counts the number of rows in a table or group.
• SUM(column) – Calculates the sum of numeric values.
• AVG(column) – Calculates the average of numeric values.
• MIN(column) – Returns the minimum value.
• MAX(column) – Returns the maximum value.

📊 Example Table: users

id | name    | age | city
---+---------+-----+---------
1  | Alice   | 22  | New York
2  | Bob     | 30  | London
3  | Charlie | 18  | Sydney
4  | Diana   | 27  | Toronto
5  | Ethan   | 35  | Paris

🔍 Example 1: Multiple Aggregate Functions

Get total users, average age, youngest and oldest users, and sum of ages:

SELECT COUNT(*) AS total_users, 
AVG(age) AS average_age, 
MIN(age) AS youngest, 
MAX(age) AS oldest, 
SUM(age) AS total_age
FROM users;

🔍 Example 2: Using GROUP BY

Get average age per city:

SELECT city, AVG(age) AS average_age
FROM users
GROUP BY city;

🔍 Example 3: Using HAVING with Aggregate Functions

Get cities with more than 1 user:

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 1;

⚡ Best Practices

• Combine GROUP BY with aggregate functions for grouped analysis.
• Use HAVING to filter groups based on aggregate results.
• Be mindful of NULL values; some functions may ignore them (e.g., AVG, SUM).
• Use descriptive aliases for the results of aggregate functions.

📝 Summary

Aggregate functions are essential for summarizing data across multiple rows. They provide powerful tools for analysis, reporting, and decision-making. By combining them with GROUP BY and HAVING, you can generate insightful summaries for specific groups of data.

🚀 Next Steps

Next, we will explore SQL Subqueries in more detail, which allow you to use the results of one query inside another query for advanced filtering.