How GROUP BY changes query execution in SQL - Quick Revision & Summary

Recall & Review

beginner

What does the GROUP BY clause do in a SQL query?

It groups rows that have the same values in specified columns into summary rows, like grouping all sales by each product.

beginner

How does GROUP BY affect the output of a query?

It changes the output from listing individual rows to showing one row per group, summarizing data for each group.

beginner

Why do we often use aggregate functions with GROUP BY?

Because GROUP BY groups rows, aggregate functions like SUM or COUNT calculate a single value for each group.

intermediate

What happens if you select columns not in GROUP BY or aggregate functions?

The query will usually give an error because SQL doesn't know how to group or summarize those columns.

intermediate

How does GROUP BY change the way SQL processes data internally?

SQL first filters rows (WHERE clause), then groups rows by the specified columns, then applies aggregate functions to each group before returning results.

What is the main purpose of the GROUP BY clause in SQL?

A To filter rows based on a condition

B To sort rows in ascending order

C To join two tables together

D To group rows with the same values into summary rows

GROUP BY groups rows that share the same values in specified columns into summary rows.

Which of the following is commonly used with GROUP BY to summarize data?

A WHERE clause

B Aggregate functions like SUM or COUNT

C ORDER BY clause

D JOIN statements

Aggregate functions calculate summary values for each group created by GROUP BY.

What happens if you select a column not in GROUP BY or an aggregate function?

A The query returns an error

B The column is ignored

C The query runs normally

D The column is automatically grouped

SQL requires all selected columns to be either grouped or aggregated; otherwise, it throws an error.

How does GROUP BY affect the number of rows returned?

A It does not change the number of rows

B It increases rows by duplicating data

C It reduces rows by grouping them

D It deletes rows from the table

GROUP BY combines rows with the same values, so fewer rows are returned as groups.

When does SQL apply the GROUP BY operation during query execution?

A After filtering rows but before selecting columns

B Before filtering rows

C After selecting columns

D After returning results

SQL filters rows first (WHERE), then groups them (GROUP BY), then selects columns and aggregates.

Explain how the GROUP BY clause changes the way SQL returns data compared to a simple SELECT.

Think about how data is summarized instead of shown row by row.

Describe what happens internally in SQL when a query includes a GROUP BY clause.

Focus on the order of operations inside SQL.

Practice

1. What does the GROUP BY clause do in an SQL query?

easy

A. It filters rows based on a condition.

B. It groups rows that have the same values in specified columns.

C. It deletes duplicate rows from the result.

D. It sorts the rows in ascending order.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same values in specified columns into groups.

2. Step 2: Compare with other SQL clauses

   Sorting is done by ORDER BY, filtering by WHERE, and removing duplicates by DISTINCT, not GROUP BY.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option B

4. Quick Check:

   GROUP BY groups rows by column values [OK]

Hint: GROUP BY groups rows by column values, not sorting or filtering [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY filters rows
• Assuming GROUP BY removes duplicates

2. Which of the following is the correct syntax to group rows by the column department?

easy

A. SELECT department, COUNT(*) FROM employees WHERE department;

B. SELECT department, COUNT(*) FROM employees ORDER BY department;

C. SELECT department, COUNT(*) FROM employees GROUP BY department;

D. SELECT department, COUNT(*) FROM employees HAVING department;

Solution

1. Step 1: Identify correct GROUP BY usage

   The GROUP BY clause must follow the FROM clause and specify the column to group by, here 'department'.

2. Step 2: Check each option's syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department; uses GROUP BY correctly. SELECT department, COUNT(*) FROM employees ORDER BY department; uses ORDER BY which sorts, not groups. SELECT department, COUNT(*) FROM employees WHERE department; uses WHERE incorrectly. SELECT department, COUNT(*) FROM employees HAVING department; uses HAVING without GROUP BY, which is invalid.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department; -> Option C

4. Quick Check:

   GROUP BY syntax: SELECT ... FROM ... GROUP BY column [OK]

Hint: GROUP BY follows FROM and lists columns to group by [OK]

Common Mistakes:

• Using ORDER BY instead of GROUP BY
• Using WHERE to group rows
• Using HAVING without GROUP BY

3. Given the table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(amount) FROM sales GROUP BY region;

medium

A. A list of regions with the total sales amount per region.

B. A list of all sales amounts without grouping.

C. An error because SUM() cannot be used with GROUP BY.

D. A list of regions sorted by amount.

Solution

1. Step 1: Understand GROUP BY with aggregate functions

   The query groups rows by 'region' and calculates the sum of 'amount' for each group.

2. Step 2: Analyze the output

   The result shows each region once with the total sales amount summed from all rows in that region.

3. Final Answer:

   A list of regions with the total sales amount per region. -> Option A

4. Quick Check:

   GROUP BY + SUM() = totals per group [OK]

Hint: GROUP BY with SUM() gives totals per group [OK]

Common Mistakes:

• Expecting all rows without grouping
• Thinking SUM() causes error with GROUP BY
• Confusing grouping with sorting

4. What is wrong with this query?

SELECT department, employee_name, COUNT(*) FROM employees GROUP BY department;

medium

A. You cannot select employee_name without grouping by it or using an aggregate function.

B. COUNT(*) cannot be used with GROUP BY.

C. GROUP BY must come before SELECT.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check columns in SELECT with GROUP BY

   When using GROUP BY on 'department', all selected columns must be grouped or aggregated.

2. Step 2: Identify the error

   'employee_name' is neither grouped nor aggregated, causing a syntax error.

3. Final Answer:

   You cannot select employee_name without grouping by it or using an aggregate function. -> Option A

4. Quick Check:

   Non-grouped columns must be aggregated [OK]

Hint: All selected columns must be grouped or aggregated [OK]

Common Mistakes:

• Selecting non-grouped columns without aggregation
• Thinking COUNT(*) is invalid with GROUP BY
• Misplacing GROUP BY clause

5. You want to find departments with more than 5 employees. Which query correctly uses GROUP BY and HAVING to achieve this?

hard

A. SELECT department, COUNT(*) FROM employees GROUP BY department WHERE COUNT(*) > 5;

B. SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5 GROUP BY department;

C. SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;

D. SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

Solution

1. Step 1: Understand HAVING clause usage

   HAVING filters groups after aggregation, so it must come after GROUP BY.

2. Step 2: Check query order and syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; correctly places HAVING after GROUP BY with condition COUNT(*) > 5. Other options misuse HAVING or WHERE clauses.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; -> Option D

4. Quick Check:

   HAVING filters groups after GROUP BY [OK]

Hint: Use HAVING after GROUP BY to filter groups [OK]

Common Mistakes:

• Using WHERE to filter aggregated results
• Placing HAVING before GROUP BY
• Confusing WHERE and HAVING clauses