Why aggregation summarizes data in MySQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this aggregation query?

Consider a table Sales with columns product_id and amount. What does this query return?

SELECT product_id, SUM(amount) FROM Sales GROUP BY product_id;

MySQL

CREATE TABLE Sales (product_id INT, amount INT);
INSERT INTO Sales VALUES (1, 100), (1, 150), (2, 200), (2, 50), (3, 300);

A [{product_id: 1, SUM(amount): 250}, {product_id: 2, SUM(amount): 50}, {product_id: 3, SUM(amount): 300}]

B [{product_id: 1, SUM(amount): 100}, {product_id: 2, SUM(amount): 200}, {product_id: 3, SUM(amount): 300}]

C [{product_id: 1, SUM(amount): 450}, {product_id: 2, SUM(amount): 250}, {product_id: 3, SUM(amount): 300}]

D [{product_id: 1, SUM(amount): 250}, {product_id: 2, SUM(amount): 250}, {product_id: 3, SUM(amount): 300}]

💡 Hint

Aggregation sums all amounts per product_id.

The query groups rows by product_id and sums the amount for each group. For product_id 1, amounts 100 + 150 = 250; for 2, 200 + 50 = 250; for 3, 300 alone.

🧠 Conceptual

intermediate

Why do we use aggregation functions in databases?

Which of the following best explains why aggregation functions like SUM, COUNT, and AVG are used in databases?

A To combine multiple rows of data into a single summary value for easier analysis.

B To delete duplicate rows from a table.

C To create new tables from existing ones.

D To change the data type of columns.

💡 Hint

Think about what happens when you want to know totals or averages.

Aggregation functions summarize many rows into one value, making it easier to understand overall data trends.

📝 Syntax

advanced

Identify the correct aggregation query syntax

Which query correctly calculates the average salary per department from a table Employees(department, salary)?

A SELECT department, salary AVG() FROM Employees GROUP BY department;

B SELECT department, AVG(salary) FROM Employees;

C SELECT department, AVG(salary) FROM Employees GROUP BY department;

D SELECT AVG(salary) FROM Employees GROUP BY department;

💡 Hint

Remember to use GROUP BY when aggregating per group.

Option C uses correct syntax: AVG function with GROUP BY to get average salary per department. Option C has wrong function syntax. Option C misses GROUP BY, so invalid. Option C selects only AVG without department column.

🔧 Debug

advanced

Why does this aggregation query cause an error?

Given a table Orders(order_id, customer_id, amount), why does this query fail?

SELECT order_id, customer_id, SUM(amount) FROM Orders GROUP BY customer_id;

A Because order_id is not included in GROUP BY or an aggregation function.

B Because SUM(amount) cannot be used with GROUP BY.

C Because customer_id should not be in GROUP BY.

D Because amount column does not exist.

💡 Hint

Check which columns appear in SELECT but not in GROUP BY or aggregation.

SQL requires all selected columns to be either aggregated or included in GROUP BY. order_id is neither, causing an error.

❓ optimization

expert

Optimizing aggregation on large datasets

You have a large Transactions table with millions of rows. Which approach best improves performance when calculating total sales per region?

A Use SELECT * with GROUP BY region to get all columns aggregated.

B Create an index on the region column before running the aggregation query.

C Run the aggregation query without any indexes to avoid overhead.

D Drop the region column to speed up aggregation.

💡 Hint

Indexes help queries filter and group data faster.

Indexing the grouping column (region) speeds up grouping and aggregation by allowing quick data access.

Practice

1. What is the main purpose of aggregation functions like SUM and COUNT in SQL?

easy

A. To create new tables from existing ones

B. To delete duplicate rows from a table

C. To combine multiple rows into a single summary value

D. To sort rows in ascending or descending order

Solution

1. Step 1: Understand aggregation functions

   Aggregation functions like SUM and COUNT take many rows and produce one value summarizing them.

2. Step 2: Identify the purpose of aggregation

   They are used to combine data, not to delete, create tables, or sort rows.

3. Final Answer:

   To combine multiple rows into a single summary value -> Option C

4. Quick Check:

   Aggregation = summary value [OK]

Hint: Aggregation means summarizing many rows into one value [OK]

Common Mistakes:

• Confusing aggregation with sorting
• Thinking aggregation deletes rows
• Believing aggregation creates new tables

2. Which of the following is the correct syntax to calculate the average salary from a table named employees?

easy

A. SELECT AVG(salary) FROM employees;

B. SELECT AVERAGE(salary) FROM employees;

C. SELECT MEAN(salary) FROM employees;

D. SELECT SUM(salary) / COUNT(*) FROM employees GROUP BY salary;

Solution

1. Step 1: Recall standard SQL aggregation functions

   The correct function for average is AVG, not AVERAGE or MEAN.

2. Step 2: Check syntax correctness

   SELECT AVG(salary) FROM employees; uses the correct AVG function with proper syntax. Options B and C use non-standard functions. SELECT SUM(salary) / COUNT(*) FROM employees GROUP BY salary; uses manual SUM/COUNT but GROUP BY salary computes per-salary value instead of overall average.

3. Final Answer:

   SELECT AVG(salary) FROM employees; -> Option A

4. Quick Check:

   AVG() is standard average function [OK]

Hint: Use AVG() for average, not AVERAGE or MEAN [OK]

Common Mistakes:

• Using non-existent functions like AVERAGE or MEAN
• Trying to calculate average manually instead of using AVG()
• Missing semicolon at the end

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

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

medium

A. Total sales amount for each region

B. List of all sales amounts without grouping

C. Average sales amount for all regions combined

D. Number of sales records in the table

Solution

1. Step 1: Understand GROUP BY with SUM

   The query groups rows by region and sums the amount in each group.

2. Step 2: Interpret the result

   It returns one row per region with the total sales amount for that region.

3. Final Answer:

   Total sales amount for each region -> Option A

4. Quick Check:

   GROUP BY region + SUM(amount) = total per region [OK]

Hint: GROUP BY groups rows; SUM adds values per group [OK]

Common Mistakes:

• Thinking it returns all individual sales
• Confusing SUM with AVG
• Ignoring GROUP BY effect

4. Identify the error in this query:

SELECT department, COUNT(employee_id) FROM employees;

medium

A. COUNT cannot be used with employee_id

B. Missing GROUP BY clause for department

C. SELECT must include WHERE clause

D. employee_id should be inside SUM()

Solution

1. Step 1: Check aggregation with non-aggregated column

   The query selects department (non-aggregated) and COUNT(employee_id) (aggregated) without GROUP BY.

2. Step 2: Understand SQL rules for aggregation

   When mixing aggregated and non-aggregated columns, GROUP BY is required on non-aggregated columns.

3. Final Answer:

   Missing GROUP BY clause for department -> Option B

4. Quick Check:

   Non-aggregated columns need GROUP BY [OK]

Hint: Use GROUP BY when selecting non-aggregated columns with aggregation [OK]

Common Mistakes:

• Omitting GROUP BY with mixed columns
• Misusing COUNT on non-numeric columns
• Adding unnecessary WHERE clause

5. You want to find the department with the highest average salary from the employees table. Which query correctly summarizes this data?

hard

A. SELECT department, MAX(AVG(salary)) FROM employees GROUP BY department;

B. SELECT department, SUM(salary) FROM employees;

C. SELECT MAX(salary) FROM employees GROUP BY department;

D. SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1;

Solution

1. Step 1: Understand the goal

   We want the department with the highest average salary, so we need average per department and then pick the top one.

2. Step 2: Analyze each option

   SELECT department, MAX(AVG(salary)) FROM employees GROUP BY department; is invalid because MAX(AVG(salary)) is not allowed directly. SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1; correctly groups by department, calculates average salary, orders descending, and limits to one result. SELECT MAX(salary) FROM employees GROUP BY department; finds max salary per department, not average. SELECT department, SUM(salary) FROM employees; sums salary but does not group or find max.

3. Final Answer:

   SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1; -> Option D

4. Quick Check:

   Group + AVG + ORDER BY + LIMIT = highest average [OK]

Hint: Use GROUP BY, AVG, ORDER BY DESC, LIMIT 1 for top average [OK]

Common Mistakes:

• Trying to nest aggregation functions incorrectly
• Not ordering results to find highest value
• Missing GROUP BY when aggregating