Before you get lost in SQL syntax and variables, remember: aggregate functions are your best buddies when it comes to counting stuff. They help you calculate totals, averages, and do all sorts of magic on big piles of rows.
Aggregate functions are used to perform math operations on groups of rows. The main ones are:
SUM(): calculates the sum of values.AVG(): calculates the average value.COUNT(): counts the number of rows in a selection.
Aggregate functions are super useful when you're working with large amounts of data and want a quick summary: how many orders you have, what's the total volume, or what was the biggest check today. Let's check out a few aggregate functions.
Counting Rows: COUNT() Function
The COUNT() function lets you count the number of rows in a table. Let's break down how it works with some examples.
-- Simple count of all rows in the orders table
SELECT COUNT(*) AS total_orders
FROM orders;
-- Counting unique customers
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;
-- Counting orders with amount greater than 100
SELECT COUNT(*) AS high_value_orders
FROM orders
WHERE total_amount > 100;
The COUNT() function is often used to count records, unique values, and also combined with filtering, like "how many students signed up for Python courses".
Summing Data: SUM() Function
The SUM() function calculates the sum of values in a column. Now let's add up all the purchases made by customers.
-- Calculating total revenue
SELECT SUM(total_amount) AS total_revenue
FROM orders;
-- Sum of purchases for a specific customer
SELECT SUM(total_amount) AS customer_spending
FROM orders
WHERE customer_id = 101;
-- Sum of orders by categories
SELECT category, SUM(total_amount) AS category_revenue
FROM orders
GROUP BY category;SUM() is your main tool for analyzing sales, revenue, and any other totals. For example, need to know the business center's income for the last month? Easy.
Averages: AVG() Function
The AVG() function helps you calculate the average value over a dataset. For example, the average student grade or the average customer check.
-- Average order amount
SELECT AVG(total_amount) AS average_order_value
FROM orders;
-- Average order amount by categories
SELECT category, AVG(total_amount) AS average_order_value
FROM orders
GROUP BY category;
-- Average customer check for the last 7 days
SELECT AVG(total_amount) AS avg_check_last_week
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days';Averages are handy for analyzing service quality, spotting anomalies, and calculating key metrics like average profit per customer.
Using Aggregate Functions in Analytics
Now that we're familiar with the main functions, let's see how to use them to build basic analytical reports.
Example 1: Total Revenue and Number of Orders
Let's say you want to know how many orders were made and what the total revenue was for the month.
SELECT
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue
FROM orders
WHERE order_date >= '2023-10-01' AND order_date <= '2023-10-31';
Example 2: Average Revenue by Category
What if we want to break down revenue by product categories?
SELECT
category,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY category;
Example 3: Orders in the Last 7 Days
It's common to analyze metrics for short periods, like the last week.
SELECT
COUNT(*) AS orders_last_week,
SUM(total_amount) AS revenue_last_week,
AVG(total_amount) AS avg_check_last_week
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days';
Practical Cases with Real Tasks
Task: Sales Analysis by Region
Let's say you own a chain of stores and want to analyze how revenue is distributed by region.
SELECT
region,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS average_order_value
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;
Task: Top 10 Customers by Revenue
Now let's add a bit of logic to highlight the top 10 customers by total order amount.
SELECT
customer_id,
SUM(total_amount) AS total_spending
FROM orders
GROUP BY customer_id
ORDER BY total_spending DESC
LIMIT 10;
Task: Comparing Revenue by Day of the Week
Want to know which days of the week your business makes the most money? Here's an example:
SELECT
TO_CHAR(order_date, 'Day') AS day_of_week,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY TO_CHAR(order_date, 'Day')
ORDER BY total_revenue DESC;
GO TO FULL VERSION