Aggregate Functions in SQL

# CHAPTER 12

Aggregate Functions in SQL #

1. Introduction #

2. Learning Objectives #

• Understand the concept of data aggregation.

• Count rows and occurrences using COUNT().

• Calculate totals using SUM().

• Find mathematical averages using AVG().

• Extract the smallest and largest values using MIN() and MAX().

3. The COUNT() Function #

-- Find out EXACTLY how many users exist in the massive database
SELECT COUNT(*) FROM users;

-- Find out how many specific users live in Texas
SELECT COUNT(*) FROM users 
WHERE state = 'TX';

*(Result: Instead of returning 5,000 rows of Texas users, it returns a single row with the number 5000!)*

4. The SUM() Function #

-- Calculate the total gross revenue of all completed orders
SELECT SUM(total_price) FROM orders 
WHERE status = 'Completed';

5. The AVG() Function #

-- Find the average salary of employees in the IT department
SELECT AVG(salary) FROM employees 
WHERE department = 'IT';

*(Tip: AVG() often returns crazy decimals like 55400.333333. You can wrap it in a ROUND() function to clean it up! e.g., ROUND(AVG(salary), 2)).*

6. The MIN() and MAX() Functions #

-- Find the price of the cheapest product we sell
SELECT MIN(price) FROM products;

-- Find the date of the most recently placed order (Highest Date)
SELECT MAX(order_date) FROM orders;

7. Combining Aggregate Functions (The Dashboard Query) #

-- Generate a complete executive summary report for 2023!
SELECT 
    COUNT(*) AS 'Total Orders Placed',
    SUM(total_price) AS 'Gross Revenue',
    AVG(total_price) AS 'Average Order Value',
    MAX(total_price) AS 'Biggest Sale'
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

8. The Danger of Mixing Aggregates with Raw Columns #

*Crash Example:* SELECT firstname, COUNT(*) FROM users; Think about it logically. COUNT(*) returns a single number (e.g., 5000). But firstname returns 5,000 separate names. SQL cannot squish 5,000 names into a single row. The database will throw a fatal syntax error.

9. Mini Project: Sales Analytics #

-- 1. How many total distinct categories of products do we sell?
SELECT COUNT(DISTINCT category) FROM inventory;

-- 2. What is the total dollar value of all inventory sitting in the warehouse?
-- (SQL can do math INSIDE the SUM function!)
SELECT SUM(price * stock_quantity) AS 'Total Asset Value' 
FROM inventory;

10. Common Mistakes #

• COUNT(column) vs COUNT(*): COUNT(*) counts rows. COUNT(email) counts *how many rows have an email address*. If 10 users exist, but 2 have NULL emails, COUNT(*) returns 10, but COUNT(email) returns 8! Aggregate functions (except COUNT(*)) completely ignore NULL values.

11. Best Practices #

• Alias Everything: Aggregate function outputs have extremely ugly column names by default (e.g., SUM(price*qty)). Always use AS 'Clean Name' to format the output for the backend developers who have to read your JSON data.

12. Exercises #

1. 1. What function is used to calculate the total mathematical sum of a numerical column?

1. 2. Why will the query SELECT username, MAX(score) FROM players; fail to execute in standard SQL?

13. SQL Challenges #

SELECT 
    COUNT(*) AS 'Active Listings',
    MIN(price) AS 'Lowest Price',
    MAX(price) AS 'Highest Price'
FROM properties
WHERE status = 'Active';

14. MCQ Quiz with Answers #

• Q: An executive asks for the Average Sales Value. You write SELECT AVG(saleamount) FROM sales;. However, 100 sales records were entered incorrectly with NULL sale amounts. Explain how the AVG() function handles these NULL values mathematically. Does it treat them as zeros, or ignore them entirely?

• Q: Explain the fatal logic error in attempting to mix raw dimensional columns with aggregate functions in the SELECT statement.

16. FAQs #

17. Summary #

18. Next Chapter Recommendation #