Counting and Summing
Aggregate functions collapse multiple rows into single summary values. They answer questions like "How many orders?" or "What's our total revenue?" Here are the core functions:
COUNT(*) — Counts all rows, including those with NULL values. Great for total record counts.
COUNT(column) — Counts non-NULL values in a column. Useful for optional fields.
SUM(column) — Adds up all numeric values. Ignores NULLs.
AVG(column) — Calculates the average of numeric values. Ignores NULLs.
MIN(column) / MAX(column) — Finds the smallest and largest values. Works with text too.
Q1: Count All Orders
──────────
300
What this shows: ShopStream has 300 total orders in the database.
Q2: Sum Revenue from Delivered Orders
──────────
157330.75
What this shows: We earned $157,330.75 from delivered orders. This filters first (WHERE), then sums only those rows.
Q3: Average Product Price
──────────
105.50
What this shows: The average product in our catalog costs about $105.50. Perfect for comparing individual products against the catalog average.
Q4: Price Range (MIN and MAX)
──────────────────────────────────
10.05 | 358.34 | 348.29
What this shows: Our cheapest item is $10.05, most expensive is $358.34, and the range is $348.29. Note we can do math with aggregates too (MAX - MIN).
GROUP BY — Grouping Rows for Per-Group Stats
Aggregate functions are powerful, but often you want stats per category, per customer, or per month. That's where GROUP BY comes in. It splits rows into groups and calculates aggregates within each group.
GROUP BY column(s) — Divide rows into groups based on column values, then apply aggregates to each group.
Q5: Product Count and Average Price by Category
──────────────────────────────────────────────
Electronics | 14 | 226.56
Clothing | 13 | 78.61
Home & Kitchen | 12 | 133.83
Books | 12 | 36.82
Toys | 10 | 67.80
Sports | 10 | 57.12
What this shows: Electronics has 14 products with an average price of $226.56, while Books has 12 products but much lower average price ($36.82). This tells us which categories are premium and which are budget-friendly.
Q6: Monthly Revenue Trend
────────────────────────────────────────
2024-01-01 00:00:00 | 19 | 16467.48
2024-02-01 00:00:00 | 8 | 5821.77
2024-03-01 00:00:00 | 15 | 9406.58
2024-04-01 00:00:00 | 14 | 11147.28
2024-05-01 00:00:00 | 27 | 17424.00
What this shows: May had the highest activity (27 orders, $17,424 revenue). February was slowest. DATE_TRUNC truncates dates to the first day of each month, then GROUP BY buckets orders into months. Perfect for trend analysis.
Filtering Groups with HAVING
You know WHERE filters rows before grouping. But what if you want to filter after grouping? For example, "Show only categories where average price exceeds $100." That's HAVING.
WHERE filters individual rows before GROUP BY is applied.
HAVING filters groups after GROUP BY and aggregates are calculated. Use it to filter on aggregate values.
Q7: Categories with Premium Average Price
──────────────────────────────
Electronics | 226.56
Home & Kitchen | 133.83
What this shows: Only Electronics and Home & Kitchen have average prices above $100. HAVING filters out Clothing, Books, Toys, and Sports because their averages fall below $100.
Pro tip: Can't use aggregate functions in WHERE — they operate on individual rows, not groups. Always use HAVING for aggregate conditions.
COUNT DISTINCT and Multi-Column Grouping
Sometimes you need unique counts (how many different customers ordered?) or to group by multiple columns (orders per status and shipping method).
Q8: Unique Customers
──────────
97
What this shows: 97 unique customers placed the 300 orders. Some customers are repeat buyers. DISTINCT eliminates duplicate customer_ids before counting.
Q9: Orders by Status and Shipping Method
───────────────────────────────
cancelled | standard | 10
cancelled | express | 10
cancelled | same_day | 3
confirmed | standard | 12
delivered | standard | 132
What this shows: Standard shipping dominates delivered orders (132). Multi-column GROUP BY splits into all combinations of status + shipping_method. Each combination gets its own count.
ROUND and Combining Everything
Aggregate results often include many decimal places. Use ROUND(column, decimals) to clean them up. Then combine GROUP BY, HAVING, JOINs, and ORDER BY for powerful analysis.
Q10: Categories with Rounded Stats
──────────────────────────────────
Home & Kitchen | 133.83 | 3.7
Books | 36.82 | 3.7
Clothing | 78.61 | 3.7
Electronics | 226.56 | 3.5
Sports | 57.12 | 3.5
Toys | 67.80 | 3.3
What this shows: Prices rounded to 2 decimals (33.83, not 133.8308333...) and ratings to 1 decimal (3.7 instead of 3.666...). Much cleaner reports!
Q11: Top 5 Customers by Spend
─────────────────────────────────────────────────────
Noah Iyer | Chicago | 6 | 5952.81
Liam Cruz | Chicago | 4 | 5189.73
Arjun Iyer | Seattle | 5 | 5094.43
Lucas Taylor | Chicago | 5 | 4972.10
Alexander White | Chicago | 7 | 4968.83
What this shows: This combines JOIN (link customers to orders), GROUP BY (stats per customer), COUNT and SUM (order count and total), and LIMIT 5 (top 5). Noah Iyer is our VIP at $5,952.81 spent. Notice Chicago dominates the top spenders — great for targeted marketing.
Challenge
Write a query to find the top 3 shipping methods by the total number of orders. Show the shipping method and order count, ordered by count descending. Hint: use GROUP BY on shipping_method, COUNT(*), and LIMIT 3.
Test Your Knowledge
You can now summarise any dataset.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →