GROUP BY with multiple columns in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to group the sales by both city and product.

MySQL

SELECT city, product, SUM(sales) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

Acity, product

Bproduct

Csales

Dcity

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by only one column when two are needed.

Using the sales column in GROUP BY instead of city and product.

The GROUP BY clause groups rows that have the same values in both city and product columns, so the sum of sales is calculated for each unique city-product pair.

2fill in blank

medium

Complete the code to select city and product, and count the number of sales entries for each group.

MySQL

SELECT city, product, COUNT(*) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

Acity, product

Bcity

Csales

Dproduct

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by only one column and getting incorrect counts.

Using COUNT(column) instead of COUNT(*) which counts all rows.

Grouping by both city and product allows counting how many sales entries exist for each unique combination.

3fill in blank

hard

Fix the error in the GROUP BY clause to correctly group by city and product.

MySQL

SELECT city, product, AVG(price) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

Aprice

Bcity product

Cproduct city

Dcity, product

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting commas between columns in GROUP BY.

Grouping by a column not in the SELECT list.

Columns in GROUP BY must be separated by commas. Writing them without commas causes a syntax error.

4fill in blank

hard

Fill both blanks to group sales by city and product and order the results by city ascending.

MySQL

SELECT city, product, SUM(sales) FROM sales_data GROUP BY [1] ORDER BY [2] ASC;

Drag options to blanks, or click blank then click option'

Acity, product

Bsales

Ccity

Dproduct

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by a column not selected or grouped.

Grouping by only one column when two are needed.

The query groups by both city and product and orders the results by city in ascending order.

5fill in blank

hard

Fill all three blanks to select city and product, count sales entries, and filter groups having more than 5 entries.

MySQL

SELECT [1], [2], COUNT(*) FROM sales_data GROUP BY [3] HAVING COUNT(*) > 5;

Drag options to blanks, or click blank then click option'

Acity

Bproduct

Ccity, product

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING to filter aggregated results.

Not grouping by all selected columns.

The query selects city and product, groups by both columns, and filters groups with more than 5 sales entries using HAVING.

Practice

1. What does the GROUP BY clause with multiple columns do in a MySQL query?

easy

A. It groups rows based on only the first column listed.

B. It groups rows based on the combination of values in all specified columns.

C. It sorts the rows without grouping them.

D. It filters rows that have duplicate values in any column.

Solution

1. Step 1: Understand GROUP BY behavior

   The GROUP BY clause groups rows that have the same values in the specified columns together.

2. Step 2: Multiple columns effect

   When multiple columns are listed, grouping happens on the combined values of all those columns, not individually.

3. Final Answer:

   It groups rows based on the combination of values in all specified columns. -> Option B

4. Quick Check:

   GROUP BY multiple columns groups by combined values [OK]

Hint: GROUP BY multiple columns groups by combined column values [OK]

Common Mistakes:

• Thinking it groups by each column separately
• Confusing GROUP BY with ORDER BY
• Assuming it filters duplicates instead of grouping

2. Which of the following is the correct syntax to group data by columns city and department in MySQL?

easy

A. SELECT city, department FROM employees GROUP BY city, department;

B. SELECT city, department FROM employees GROUP city, department;

C. SELECT city, department FROM employees GROUP BY city department;

D. SELECT city, department FROM employees GROUP BY (city, department);

Solution

1. Step 1: Identify correct GROUP BY syntax

   The correct syntax uses GROUP BY followed by column names separated by commas.

2. Step 2: Check each option

   SELECT city, department FROM employees GROUP BY city, department; uses correct syntax with commas. Options A, B, and D have syntax errors or wrong keywords.

3. Final Answer:

   SELECT city, department FROM employees GROUP BY city, department; -> Option A

4. Quick Check:

   GROUP BY columns separated by commas [OK]

Hint: Use commas to separate columns in GROUP BY [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong keywords like GROUP instead of GROUP BY
• Using parentheses incorrectly around columns

3. Given the table sales with columns region, product, and amount, what will this query return?

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

medium

A. Syntax error due to missing alias for SUM(amount).

B. Total sales amount for each region only.

C. Total sales amount for each product only.

D. Total sales amount for each region and product combination.

Solution

1. Step 1: Understand GROUP BY with multiple columns

   The query groups rows by both region and product together.

2. Step 2: Apply SUM aggregation

   Within each group, it sums the amount values, giving total sales per region-product pair.

3. Final Answer:

   Total sales amount for each region and product combination. -> Option D

4. Quick Check:

   GROUP BY multiple columns sums per combined group [OK]

Hint: GROUP BY multiple columns sums per combined group [OK]

Common Mistakes:

• Thinking it sums only by one column
• Expecting syntax error without alias (alias optional)
• Confusing GROUP BY with WHERE filtering

4. Identify the error in this query:

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

medium

A. Missing department in GROUP BY causes error or incorrect grouping.

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

C. SELECT must include only aggregated columns.

D. GROUP BY should be after WHERE clause.

Solution

1. Step 1: Check SELECT and GROUP BY columns

   The query selects city and department but groups only by city.

2. Step 2: Understand grouping rules

   All non-aggregated columns in SELECT must appear in GROUP BY to avoid errors or unpredictable results.

3. Final Answer:

   Missing department in GROUP BY causes error or incorrect grouping. -> Option A

4. Quick Check:

   All selected non-aggregated columns must be in GROUP BY [OK]

Hint: Include all non-aggregated SELECT columns in GROUP BY [OK]

Common Mistakes:

• Selecting columns not in GROUP BY without aggregation
• Thinking COUNT(*) is invalid with GROUP BY
• Misplacing GROUP BY clause

5. You have a transactions table with columns store_id, employee_id, and sales_amount. You want to find the total sales per store and employee but only for employees with total sales above 1000. Which query correctly achieves this?

hard

A. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id HAVING SUM(sales_amount) > 1000;

B. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions WHERE SUM(sales_amount) > 1000 GROUP BY store_id, employee_id;

C. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id, employee_id HAVING SUM(sales_amount) > 1000;

D. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY employee_id, store_id WHERE SUM(sales_amount) > 1000;

Solution

1. Step 1: Group by both store_id and employee_id

   We need totals per store and employee, so GROUP BY both columns together.

2. Step 2: Filter groups with HAVING clause

   Use HAVING to filter groups where total sales exceed 1000. WHERE cannot filter aggregated results.

3. Final Answer:

   SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id, employee_id HAVING SUM(sales_amount) > 1000; -> Option C

4. Quick Check:

   Use HAVING to filter aggregated groups [OK]

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

Common Mistakes:

• Using WHERE to filter aggregated results
• Grouping by only one column instead of both
• Placing WHERE after GROUP BY