Aggregate Functions in PostgreSQL

# CHAPTER 10

Aggregate Functions in PostgreSQL #

1. Introduction #

2. Learning Objectives #

• Count the total number of rows in a table using COUNT().

• Add numerical values together using SUM().

• Calculate the mathematical average using AVG().

• Find the absolute highest and lowest values using MAX() and MIN().

• Combine aggregate functions with the WHERE clause.

3. The COUNT() Function #

-- How many total employees work at the company?
SELECT COUNT(*) FROM employees;

-- How many employees work specifically in the IT department?
SELECT COUNT(*) FROM employees WHERE department = 'IT';

*(Notice the * inside COUNT. This tells PostgreSQL to count rows regardless of what data is in them).*

4. The SUM() Function #

-- What is the total monthly payroll for the entire company?
SELECT SUM(salary) FROM employees;

-- How much total revenue did Product #5 generate yesterday?
SELECT SUM(total_price) FROM orders 
WHERE product_id = 5 AND order_date = current_date;

5. The AVG() Function #

-- What is the average salary of an employee in this company?
SELECT AVG(salary) FROM employees;

*Note: AVG() often returns a massive decimal (e.g., 55333.333333333). In PostgreSQL, you can use the ROUND() function to clean it up: SELECT ROUND(AVG(salary), 2) FROM employees;*

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

-- Who is the highest paid person?
SELECT MAX(salary) FROM employees;

-- What is the most expensive product we sell?
SELECT MAX(price) FROM products;

-- What is the date of the very first order ever placed?
SELECT MIN(order_date) FROM orders;

7. Combining Aggregates #

SELECT 
    COUNT(*) AS total_orders,
    SUM(total_price) AS total_revenue,
    AVG(total_price) AS average_order_value,
    MAX(total_price) AS biggest_sale
FROM orders
WHERE order_date >= '2023-01-01';

8. Mini Project: Sales Analytics Dashboard #

SELECT 
    COUNT(order_id) AS total_sales,
    SUM(amount) AS gross_revenue,
    ROUND(AVG(amount), 2) AS aov,
    MIN(amount) AS smallest_purchase
FROM transactions
WHERE status = 'Completed' 
AND transaction_date BETWEEN '2023-10-01' AND '2023-10-31';

9. Common Mistakes #

• Mixing Aggregates with Normal Columns: This is the #1 error beginners make. You CANNOT write SELECT firstname, COUNT(*) FROM employees;. Think about it logically: COUNT(*) returns exactly ONE row (the total number). firstname wants to return 1,000 rows (every single name). The database cannot smash 1,000 rows and 1 row into the same grid. It will crash. (We will learn the workaround for this in Chapter 11).

• NULL values in AVG(): Aggregate functions automatically ignore NULL values. If 5 employees have a salary, and 5 have NULL (missing) salaries, AVG(salary) will calculate the average of the 5 who have it, NOT divide by 10.

10. Best Practices #

• Always use Aliases: Aggregate output column names are incredibly ugly (e.g., sum, avg). Always use AS "Total" so your frontend application receives a clean JSON key.

11. Exercises #

1. 1. Write a query to find the total combined inventory (quantity) of all items in the products table.

1. 2. Write a query to find the hire date of the newest (most recently hired) employee.

12. SQL Challenges #

SELECT SUM(price * quantity) AS "Total Spent" 
FROM order_items 
WHERE user_id = 42;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain why executing SELECT department, SUM(salary) FROM employees; will result in a PostgreSQL syntax error.

• Q: In a massive E-commerce database, describe how you would calculate the Average Order Value (AOV) for a specific marketing campaign.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #