9 minutes·Lesson 4 of 5
Aggregations and Grouping
Aggregate Functions
Aggregate functions perform calculations across a group of rows and return a single value. They are the foundation of analytical SQL.
| Function | Description | Example |
|---|---|---|
| COUNT(*) | Count all rows | COUNT(*) AS total_orders |
| COUNT(column) | Count non-NULL values | COUNT(email) AS has_email |
| SUM(column) | Total of all values | SUM(amount) AS total_revenue |
| AVG(column) | Average of all values | AVG(amount) AS avg_order |
| MIN(column) | Smallest value | MIN(price) AS lowest_price |
| MAX(column) | Largest value | MAX(price) AS highest_price |
COUNT(*) vs COUNT(column)
- COUNT(*) counts all rows, including those with NULL values
- COUNT(column) counts only rows where that column is not NULL
SELECT
COUNT(*) AS total_customers,
COUNT(email) AS customers_with_email,
COUNT(*) - COUNT(email) AS missing_email
FROM customers;
GROUP BY: Aggregating by Category
GROUP BY groups rows with the same value in specified columns and applies aggregate functions to each group.
-- Total revenue and order count by country
SELECT
country,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY country
ORDER BY total_revenue DESC;
The GROUP BY Rule
Every column in SELECT must either:
- Be in the GROUP BY clause, or
- Be wrapped in an aggregate function
-- This is WRONG: customer_name is not in GROUP BY or an aggregate
SELECT customer_id, customer_name, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;
-- This is CORRECT: customer_name is also in GROUP BY
SELECT customer_id, customer_name, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, customer_name;
Grouping by Multiple Columns
-- Revenue by country and product category
SELECT
country,
category,
SUM(total_amount) AS revenue
FROM orders
GROUP BY country, category
ORDER BY country, revenue DESC;
CASE WHEN: Conditional Calculations
CASE WHEN creates conditional expressions in SQL, similar to IF in Excel:
SELECT
order_id,
total_amount,
CASE
WHEN total_amount >= 1000 THEN 'Large'
WHEN total_amount >= 200 THEN 'Medium'
ELSE 'Small'
END AS order_size
FROM orders;
Using CASE WHEN with GROUP BY for Pivot-style Analysis
-- Count orders by size segment
SELECT
CASE
WHEN total_amount >= 1000 THEN 'Large'
WHEN total_amount >= 200 THEN 'Medium'
ELSE 'Small'
END AS order_size,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue
FROM orders
GROUP BY
CASE
WHEN total_amount >= 1000 THEN 'Large'
WHEN total_amount >= 200 THEN 'Medium'
ELSE 'Small'
END
ORDER BY total_revenue DESC;
Conditional Counting (Most Powerful Pattern)
-- Count by status in a single row using conditional SUM
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;
This technique pivots row-level data into columns without a separate PIVOT clause.
Window Functions: Analytics Without Grouping
Window functions perform calculations across a set of rows related to the current row, without collapsing the result to one row per group (unlike GROUP BY).
-- Add a running total column without collapsing rows
SELECT
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Rank within Groups
-- Rank customers by revenue within each country
SELECT
customer_id,
country,
total_spend,
RANK() OVER (PARTITION BY country ORDER BY total_spend DESC) AS rank_in_country
FROM customer_summary;
Common Window Functions
- ROW_NUMBER(): Unique sequential number within partition
- RANK(): Rank with gaps (1, 2, 2, 4)
- DENSE_RANK(): Rank without gaps (1, 2, 2, 3)
- LAG(column, n): Value from n rows before the current row
- LEAD(column, n): Value from n rows after the current row
- SUM() OVER (): Running total or group total alongside individual rows
Month-over-Month Growth Using LAG
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS pct_change
FROM monthly_revenue
ORDER BY month;
Common Analytical Patterns
Top N per Group (most popular pattern in analytical SQL)
-- Top 3 products by revenue in each category
WITH ranked AS (
SELECT
category,
product_name,
SUM(revenue) AS total_revenue,
RANK() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS rank
FROM sales
GROUP BY category, product_name
)
SELECT * FROM ranked WHERE rank <= 3;
Percentage of Total
SELECT
country,
SUM(revenue) AS country_revenue,
ROUND(SUM(revenue) / SUM(SUM(revenue)) OVER () * 100, 1) AS pct_of_total
FROM orders
GROUP BY country
ORDER BY country_revenue DESC;
Key Takeaways
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) reduce many rows to a single result value.
- GROUP BY groups rows by specified columns and applies aggregate functions to each group -- every non-aggregated SELECT column must be in GROUP BY.
- CASE WHEN adds conditional logic: use it for segmentation, labelling, and conditional counting/summing.
- Window functions (OVER) perform aggregations without collapsing rows -- essential for rankings, running totals, and period comparisons.
- The conditional SUM pattern -- SUM(CASE WHEN condition THEN 1 ELSE 0 END) -- is one of the most useful patterns for pivoting row data into columns.
Practice Exercise
Using a sales table with columns: order_id, customer_id, product_id, category, country, revenue, order_date, status:
- Write a query showing total revenue, order count, and average order value by country, sorted by revenue descending
- Write a query that segments orders into 'Large' (>$500), 'Medium' ($100-$500), and 'Small' (<$100) and counts how many orders fall in each segment
- Write a query showing monthly revenue with a running total and month-over-month percentage change
- Write a query showing total orders, completed orders, and cancelled orders as separate columns in a single row using conditional SUM
Try it yourself
Preview
Key Takeaways
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) reduce many rows to a single value -- use them with GROUP BY for per-segment analysis.
- The GROUP BY rule: every column in SELECT must either be in GROUP BY or inside an aggregate function.
- CASE WHEN adds conditional logic: use for segmentation and the conditional SUM pattern (SUM(CASE WHEN ... THEN 1 ELSE 0 END)).
- Window functions (OVER clause) add aggregates to each row without collapsing -- essential for rankings, running totals, and period comparisons.
- LAG() and LEAD() access previous/next row values in a window -- the standard approach for period-over-period growth calculations.
Quick Quiz
1.What is the GROUP BY rule in SQL?
2.What is the difference between COUNT(*) and COUNT(column)?
3.What do window functions (OVER clause) do that regular GROUP BY cannot?
4.Which SQL pattern is most useful for counting orders by status (completed, pending, cancelled) in a single result row?
Ready to go further?
CareerEx gives you structured 12-week training, live classes every Saturday and Sunday, real tutor feedback, and a certificate. Join the next cohort.
Join CareerEx