Introduction to Aggregate Functions: SQL Math for Analytics

Previously in this course, we mastered Introduction to SELECT Queries: Data Retrieval in PostgreSQL to fetch individual records. While retrieving rows is essential, real-world applications often require us to synthesize that data into high-level insights. In this lesson, we move beyond row-level retrieval to perform calculations across entire datasets.

What are Aggregate Functions?

Aggregate functions take multiple rows of data as input and return a single, summarized value. Instead of asking "What was the price of this specific product?", we ask "What is the average price of all products?" or "What is the total revenue from all orders?".

These functions are the bedrock of SQL math and business analytics. PostgreSQL provides a robust set of built-in functions designed for these tasks.

Core Aggregate Functions for Your Store

In our store application, we typically care about three fundamental metrics: volume, total value, and performance averages.

1. Counting Rows with COUNT()

The COUNT() function returns the number of rows that match your criteria. It is the most frequent way to determine the size of a dataset.

SQL
-- Count total number of products in our catalog
SELECT COUNT(*) FROM products;

-- Count only products that are currently in stock
SELECT COUNT(*) FROM products WHERE stock_quantity > 0;

2. Summing Values with SUM()

When you need to know the total value of a numeric column, use SUM(). This is essential for financial reporting.

SQL
-- Calculate the total potential revenue if all items sold
SELECT SUM(price * stock_quantity) AS total_inventory_value 
FROM products;

3. Calculating Averages with AVG()

To understand the "typical" value in your data—such as the average order price or average product cost—use AVG().

SQL
-- What is the average price of a product in our store?
SELECT AVG(price) FROM products;

Worked Example: Calculating Store Revenue

Let's apply these to our store project. Imagine we have an order_items table with columns order_id, product_id, quantity, and unit_price. To calculate the total revenue generated by the store, we need to multiply the price by the quantity for every line item and then sum those results.

SQL
-- Calculate total revenue from all orders
SELECT 
    COUNT(order_id) AS total_line_items,
    SUM(quantity * unit_price) AS total_store_revenue
FROM order_items;

In this query:

• COUNT(order_id) tells us how many individual line items have been processed.
• SUM(quantity * unit_price) performs the math row-by-row and adds the results together to give us a grand total.

Hands-on Exercise

Using your own store database environment (set up in Setting Up Your PostgreSQL Environment), try to perform the following analysis:

1. Find the total number of orders placed in your orders table.
2. Calculate the average quantity of items ordered across your entire order_items table.
3. Bonus: Can you find the total amount spent by a specific customer? (Hint: Use a WHERE clause with your SUM() function).

Common Pitfalls to Avoid

• NULL Values: Be aware that COUNT(*) counts every row, but COUNT(column_name) ignores NULL values in that specific column. Always use COUNT(*) unless you specifically need to exclude missing data.
• Mixing Aggregates and Non-Aggregates: If you try to run SELECT product_name, SUM(price) FROM products, PostgreSQL will throw an error. You cannot mix columns that return individual values with aggregate functions unless you use a GROUP BY clause (which we will cover in the next lesson).
• Integer Division: In some SQL dialects, dividing two integers results in an integer (e.g., 5 / 2 = 2). PostgreSQL handles this gracefully, but if you find your averages look "truncated," cast your columns to NUMERIC to ensure floating-point precision.

FAQ

Can I use aggregate functions in a WHERE clause? No. The WHERE clause filters data before aggregation happens. If you want to filter based on an aggregated result (e.g., "only show products with an average rating above 4"), you must use the HAVING clause, which we will explore shortly.

Do aggregate functions work on text columns? COUNT() works on text columns, but SUM() and AVG() will fail because they require numeric input.

Recap

We have moved from simple data retrieval to data synthesis. By using COUNT(), SUM(), and AVG(), we can transform raw database records into high-level metrics. These tools allow you to report on store health, calculate inventory worth, and track sales performance effectively.

Up next: Grouping Data with GROUP BY to break these metrics down by customer or category.