Total Track Count
The product manager wants to know the total size of the music catalog. How many tracks are available in the store?...
COUNTAggregation
Beginner
3m10 pts
34100%
SQL Practice Hub
SQL Aggregation and GROUP BY Practice Questions
Collapse many rows into one summary row per group.
59 questionsFreeRuns in your browser
- GROUP BY
- Names the columns that define each group.
- SUM, COUNT, AVG
- Describe each group with one number (also MIN and MAX).
- HAVING
- Filters groups after aggregation; WHERE cannot use aggregates.
What you will practice
- Know when a column must appear in GROUP BY and why the database rejects it otherwise
- Use WHERE to filter rows before grouping and HAVING to filter groups afterwards
- Understand how COUNT(*), COUNT(column) and COUNT(DISTINCT column) differ on NULLs and duplicates
- Write conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END) to pivot a column into metrics
- Combine aggregates with joins without double-counting the fact table
Read the full overview
Aggregation collapses many rows into one summary row per group: GROUP BY names the grouping columns, and SUM, COUNT, AVG, MIN and MAX describe each group. HAVING then filters the groups themselves, which is why it can reference an aggregate while WHERE cannot. This is the largest topic in the question bank because almost every analyst task, from revenue by month to active users per plan, is an aggregation with the right grouping key.
59 questionsPage 1 of 4
Track Count by Genre
The content team wants to understand the distribution of tracks across different Genre. Which genres have the most...
GROUP BYCOUNTJOINAggregationIntermediate6m20 pts47100%Average Song Duration by Genre
The data science team is analyzing listening patterns. They want to know the average track length for each genre. Some...
GROUP BYAVGJOINAggregationIntermediate6m20 pts43100%Top 5 Countries by Sales
The executive team wants to know which countries generate the most revenue. Create a report showing total sales by...
GROUP BYSUMAggregationLIMITBeginner7m10 pts4698%Tracks Above Average Price
The pricing team wants to identify premium-priced Track. Find all tracks that are priced above the average track price...
SubqueryAVGWHEREBeginner6m15 pts45100%Most Prolific Artists
The editorial team is writing a feature about the most prolific artists in the catalog. Find artists who have released...
GROUP BYHAVINGJOINAggregationIntermediate8m20 pts4298%Rank Artists by Album Count
Create a leaderboard of artists ranked by how many albums they've released. Artists with the same number of albums...
Window FunctionsRANKGROUP BYJOIN+1Intermediate8m25 pts3297%Running Total of Invoice Amounts
Calculate a running total (cumulative sum) of invoice amounts for each customer, showing how their total spending...
Window FunctionsRunning TotalSUMPARTITION BYIntermediate7m25 pts33100%Segment Customers into Quartiles
The marketing team wants to segment customers into 4 groups (quartiles) based on their total spending. This will help...
Window FunctionsNTILEGROUP BYJOIN+1Intermediate10m25 pts27100%Basic CTE: Artist Album Counts
Common Table Expressions (CTEs) let you define named subqueries that make complex queries more readable. Let's start...
CTEWITHGROUP BYIntermediate7m25 pts2596%CTE: Top Tracks Per Genre
Use a CTE to find the longest track in each genre. CTEs are especially useful when you need to reference aggregated...
CTEWITHMAXJOINAdvanced10m35 pts29100%Correlated Subquery: Above Genre Average
A correlated subquery references the outer query. Find tracks that are longer than the average length of tracks in...
SubqueryCorrelated SubqueryAVGIntermediate9m25 pts22100%CASE in Aggregation: Count by Category
Use CASE within aggregation functions to count items by category in a single query. Count tracks by price tier without...
CASE WHENAggregationSUMConditional CountingIntermediate7m20 pts21100%Multiple Aggregations in One Query
Calculate multiple statistics about tracks in a single query: count, average length, min price, max price, and total...
AggregationCOUNTAVGMIN+2Beginner5m10 pts23100%GROUP BY Multiple Columns
Group data by multiple columns to get more granular insights. Calculate the number of invoices and total revenue by...
GROUP BYMultiple ColumnsDate FunctionsEXTRACTBeginner7m15 pts1594%
FAQ
SQL Aggregation and GROUP BY practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
- WHERE cannot use aggregates like SUM or COUNT; HAVING can.
- WHERE also shrinks the data before GROUP BY runs, so it is cheaper.
- Rule of thumb: put every condition you can in WHERE, and keep HAVING for aggregate conditions.
SQL
SELECT region, SUM(amount) AS total
FROM orders
WHERE status = 'paid' -- row filter, before grouping
GROUP BY region
HAVING SUM(amount) > 1000; -- group filter, after groupingWhat is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
They count different things: all rows, non-NULL values, or unique non-NULL values.
- COUNT(*): every row in the group, NULLs included.
- COUNT(column): only rows where that column is not NULL.
- COUNT(DISTINCT column): unique non-NULL values, what you want for how many customers after a join repeats them.
- Mixing them up is a common cause of inflated or understated metrics.
SQL
SELECT COUNT(*) AS order_rows,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT user_id) AS unique_customers
FROM orders;Why do I get an error when I select a column that is not in GROUP BY?
Every selected column must be in GROUP BY or inside an aggregate, because the database cannot pick one value for the whole group.
- Add the column to GROUP BY if you want one row per combination (shown here).
- Wrap it in MIN or MAX if any value in the group will do.
- Use a window function if you need every row next to the aggregate.
- MySQL with ONLY_FULL_GROUP_BY off silently picks a random value, which hides the bug.
SQL
-- error: name is neither grouped nor aggregated
SELECT customer_id, name, SUM(amount) FROM sales GROUP BY customer_id;
-- fix: group by it as well
SELECT customer_id, name, SUM(amount) FROM sales GROUP BY customer_id, name;Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.