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 2 of 4

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 grouping

What 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;

Track your streak & earn XP

Full-Length Interview Practice Tests100% Free

10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.

Explore 10 Mock Tests

SQL Practice Hub

SQL Aggregation and GROUP BY Practice Questions

Leaderboard

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

59 questionsPage 2 of 4

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 grouping

What 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;

Track your streak & earn XP

Full-Length Interview Practice Tests100% Free

10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.

Explore 10 Mock Tests

SQL Aggregation and GROUP BY Practice Questions

What you will practice

HAVING with Multiple Conditions

Nested Aggregation: Average of Totals

Calculate Percentage of Total

LEFT JOIN: All Artists Including Those Without Albums

COUNT DISTINCT: Unique Composers in Rock

MIN/MAX per Group

HAVING vs WHERE: Filter Before and After Grouping

Revenue Share by Genre with Window Functions

Customer Spending vs Country Average

Customer Lifetime Value with Tier Classification

Support Rep Sales Performance Comparison

Customer Genre Diversity Score

Artists Spanning Multiple Genres

Customer Purchase Gap Analysis

Pareto Analysis: Top 20% Customers Revenue Share

SQL Aggregation and GROUP BY practice: common questions

Full-Length Interview Practice Tests100% Free

SQL Aggregation and GROUP BY Practice Questions

What you will practice

HAVING with Multiple Conditions

Nested Aggregation: Average of Totals

Calculate Percentage of Total

LEFT JOIN: All Artists Including Those Without Albums

COUNT DISTINCT: Unique Composers in Rock

MIN/MAX per Group

HAVING vs WHERE: Filter Before and After Grouping

Revenue Share by Genre with Window Functions

Customer Spending vs Country Average

Customer Lifetime Value with Tier Classification

Support Rep Sales Performance Comparison

Customer Genre Diversity Score

Artists Spanning Multiple Genres

Customer Purchase Gap Analysis

Pareto Analysis: Top 20% Customers Revenue Share

SQL Aggregation and GROUP BY practice: common questions

Full-Length Interview Practice Tests100% Free

SQL Aggregation and GROUP BY Practice Questions

What you will practice

HAVING with Multiple Conditions

Nested Aggregation: Average of Totals

Calculate Percentage of Total

LEFT JOIN: All Artists Including Those Without Albums

COUNT DISTINCT: Unique Composers in Rock

MIN/MAX per Group

HAVING vs WHERE: Filter Before and After Grouping

Revenue Share by Genre with Window Functions

Customer Spending vs Country Average

Customer Lifetime Value with Tier Classification

Support Rep Sales Performance Comparison

Customer Genre Diversity Score

Artists Spanning Multiple Genres

Customer Purchase Gap Analysis

Pareto Analysis: Top 20% Customers Revenue Share

SQL Aggregation and GROUP BY practice: common questions

Related practice topics

Full-Length Interview Practice Tests100% Free

SQL Aggregation and GROUP BY Practice Questions

What you will practice

HAVING with Multiple Conditions

Nested Aggregation: Average of Totals

Calculate Percentage of Total

LEFT JOIN: All Artists Including Those Without Albums

COUNT DISTINCT: Unique Composers in Rock

MIN/MAX per Group

HAVING vs WHERE: Filter Before and After Grouping

Revenue Share by Genre with Window Functions

Customer Spending vs Country Average

Customer Lifetime Value with Tier Classification

Support Rep Sales Performance Comparison

Customer Genre Diversity Score

Artists Spanning Multiple Genres

Customer Purchase Gap Analysis

Pareto Analysis: Top 20% Customers Revenue Share

SQL Aggregation and GROUP BY practice: common questions

Related practice topics

Full-Length Interview Practice Tests100% Free