Introduction to Aggregate Functions

The Shopkeeper's Calculator

The Big Picture

In business, we rarely look at every single transaction. Instead, we need the Aggregate View. Think of a shopkeeper at the end of the day: they don't just re-read every receipt; they use a calculator to count the slips, sum the cash, and find the average spent.

Welcome to Module 3! To understand SQL aggregations, imagine a shopkeeper with a growing stack of paper receipts. Instead of reading every single one, they use a calculator to get the 'big picture.' SQL aggregate functions are that digital calculator for your database, allowing you to turn thousands of rows into a single, meaningful insight.

• Aggregate functions collapse multiple rows into one number.
• They provide high-level business metrics.
• Essential for answering 'How many' and 'How much' questions.

The Orders Table Schema

Our Data Source

We will use the Orders table. It contains the transaction history for our E-commerce store.

• order_id: Unique ID
• customer_id: Who bought it
• total_amount: Price paid

Here is our Orders table. Each row represents one sale. To find our total revenue, we'll need to look at the total_amount column. To find how many sales we made, we'll look at the rows themselves. Let's look at the three functions that make this possible. AVG calculates the mathematical mean. SUM adds up every value in a numeric column. COUNT returns the total number of rows.

• Aggregate functions work on columns.
• Numeric columns like total_amount are best for SUM and AVG.
• Any column can be counted.

Counting Records with COUNT(*)

How many orders?

To find the total number of records, use COUNT(*). The asterisk tells SQL to count every row, regardless of its content.

SELECT COUNT(*) AS total_orders
FROM Orders;

To find out how many total orders have been placed, we use SELECT COUNT asterisk. Notice the 'AS' keyword? We use that to give our result a friendly name like 'total_orders' so the output is easy to read.

• COUNT(*) counts every row in the result set.
• The AS keyword creates a readable alias for the result.
• It is the most common way to get volume metrics.

Calculating Financial Metrics

Revenue and AOV

Use SUM() for totals and AVG() for means. These only work on numeric data types.

SELECT SUM(total_amount) AS total_revenue,
       AVG(total_amount) AS avg_order_value
FROM Orders;

Now let's talk money. To find our total revenue, we use SUM on the total_amount column. To see the average amount spent per transaction, we use AVG. You can even calculate both in a single query by separating them with a comma.

• SUM adds numeric values together.
• AVG finds the average.
• You can run multiple aggregate functions in one SELECT statement.

Common Pitfalls: NULLs and Types

Watch Your Step!

Aggregates behave differently with NULL values and specific data types.

• COUNT(*): Counts every row.
• COUNT(column): Ignores rows where that column is NULL.
• Data Types: You cannot SUM a text column like 'customer_name'.

Be careful with NULL values. If a row has a missing value in a column, COUNT of that column will skip it, but COUNT asterisk will still count the row. Also, remember your data types! You can't calculate the average of a list of names—SQL will throw an error if you try to SUM text.

• COUNT(*) vs COUNT(column_name) is a common source of error.
• Aggregates ignore NULLs (except for COUNT(*)).
• Always ensure your column is numeric before using SUM or AVG.

Exercise 1: Daily Order Count

Challenge 1

Write a query to COUNT how many orders were placed on '2024-01-01'.

Hint: Use a WHERE clause to filter the date first.

It's your turn. Write a query to count the orders from January 1st, 2024. Remember to use the WHERE clause we learned in the previous module to filter the rows before counting them.

• Combining WHERE with COUNT.
• Filtering by date strings.

Exercise 2: Customer Insights

Challenge 2

Calculate the total revenue (SUM) and the average order value (AVG) for customer_id 5 in a single query.

Use aliases to name your columns total_spent and avg_spent.

Let's get a bit more specific. Can you find the total amount spent and the average spent for just customer number 5? Don't forget to use 'AS' to rename your columns as total_spent and avg_spent.

• Multiple aggregates in one query.
• Using column aliases (AS).
• Filtering for a specific ID.