Combining multiple aggregates in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate total sales and average sales per customer

Given a table sales with columns customer_id and amount, what is the output of this query?

SELECT customer_id, SUM(amount) AS total_sales, AVG(amount) AS avg_sales FROM sales GROUP BY customer_id ORDER BY customer_id;

SQL

SELECT customer_id, SUM(amount) AS total_sales, AVG(amount) AS avg_sales FROM sales GROUP BY customer_id ORDER BY customer_id;

A [{"customer_id": 1, "total_sales": 300, "avg_sales": 300.0}, {"customer_id": 2, "total_sales": 450, "avg_sales": 450.0}]

B [{"customer_id": 1, "total_sales": 300, "avg_sales": 150.0}, {"customer_id": 2, "total_sales": 450, "avg_sales": 225.0}]

C [{"customer_id": 1, "total_sales": 150, "avg_sales": 75.0}, {"customer_id": 2, "total_sales": 225, "avg_sales": 112.5}]

D [{"customer_id": 1, "total_sales": 300, "avg_sales": null}, {"customer_id": 2, "total_sales": 450, "avg_sales": null}]

💡 Hint

Remember that AVG calculates the average of the amounts per customer.

The query groups sales by customer_id, then calculates the sum and average of amounts for each customer. Option B correctly shows total and average sales per customer.

📝 Syntax

intermediate

Identify the syntax error in combining aggregates

Which option contains a syntax error when combining multiple aggregate functions in one SELECT statement?

SQL

SELECT department, COUNT(employee_id), MAX(salary) FROM employees GROUP BY department;

A SELECT department, COUNT(employee_id), MAX(salary) FROM employees GROUP BY;

B SELECT department, COUNT(employee_id), MAX(salary) FROM employees;

C SELECT department, COUNT(employee_id), MAX(salary) FROM employees GROUP BY department, salary;

D SELECT department, COUNT(employee_id), MAX(salary) FROM employees GROUP BY department;

💡 Hint

GROUP BY clause must specify columns to group by.

Option A has 'GROUP BY' without any column, which is a syntax error. Other options are syntactically valid though some may produce different results.

❓ optimization

advanced

Optimize query combining multiple aggregates

You want to get the total number of orders and the maximum order amount per customer from the orders table. Which query is the most efficient?

A SELECT customer_id, COUNT(order_id) + MAX(amount) FROM orders GROUP BY customer_id;

B SELECT customer_id, (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o1.customer_id), (SELECT MAX(amount) FROM orders o3 WHERE o3.customer_id = o1.customer_id) FROM orders o1 GROUP BY customer_id;

C SELECT customer_id, COUNT(order_id) OVER (PARTITION BY customer_id), MAX(amount) OVER (PARTITION BY customer_id) FROM orders;

D SELECT customer_id, COUNT(order_id), MAX(amount) FROM orders GROUP BY customer_id;

💡 Hint

Try to avoid subqueries or window functions if simple GROUP BY works.

Option D uses GROUP BY with aggregates directly, which is efficient. Option D uses correlated subqueries causing performance issues. Option D uses window functions returning repeated rows. Option D adds count and max, which is logically incorrect.

🧠 Conceptual

advanced

Understanding NULL handling in multiple aggregates

Given a table payments with some NULL values in the amount column, which aggregate function combination correctly counts all rows and sums only non-NULL amounts?

A SELECT COUNT(amount), SUM(amount) FROM payments;

B SELECT COUNT(amount), SUM(COALESCE(amount, 0)) FROM payments;

C SELECT COUNT(*), SUM(amount) FROM payments;

D SELECT COUNT(*), SUM(COALESCE(amount, 0)) FROM payments;

💡 Hint

COUNT(column) counts only non-NULL values, COUNT(*) counts all rows.

COUNT(*) counts all rows including those with NULL in amount. SUM(amount) sums only non-NULL amounts. Option C correctly counts all rows and sums non-NULL amounts.

🔧 Debug

expert

Why does this query produce incorrect results combining aggregates?

Consider this query:

SELECT department, COUNT(employee_id), AVG(salary) FROM employees;

Why does it produce an error or incorrect results?

SQL

SELECT department, COUNT(employee_id), AVG(salary) FROM employees;

A Because department is not in GROUP BY clause, causing aggregation error.

B Because COUNT(employee_id) cannot be used with AVG(salary) together.

C Because the query needs a HAVING clause to filter groups.

D Because AVG(salary) requires DISTINCT keyword to work.

💡 Hint

When using aggregates with other columns, those columns must be grouped.

Selecting a non-aggregated column without GROUP BY causes SQL error or undefined behavior. Option A correctly identifies the missing GROUP BY.

Practice

1. What does combining multiple aggregate functions in a single SQL query allow you to do?

easy

A. Get several summary values like totals and averages in one result

B. Run multiple queries at the same time

C. Create new tables automatically

D. Sort data without using ORDER BY

Solution

1. Step 1: Understand aggregate functions

   Aggregate functions like SUM() and AVG() calculate summary values from data.

2. Step 2: Combining aggregates in one query

   Using commas, you can list multiple aggregates in SELECT to get many summaries at once.

3. Final Answer:

   Get several summary values like totals and averages in one result -> Option A

4. Quick Check:

   Multiple aggregates = multiple summaries [OK]

Hint: Use commas to separate aggregates in SELECT [OK]

Common Mistakes:

• Thinking multiple queries run simultaneously
• Confusing aggregates with table creation
• Assuming aggregates sort data automatically

2. Which of the following is the correct syntax to combine multiple aggregates in one SQL SELECT statement?

easy

A. SELECT SUM(price) AND AVG(price) FROM sales;

B. SELECT SUM(price), AVG(price) FROM sales;

C. SELECT SUM(price) AVG(price) FROM sales;

D. SELECT SUM(price) OR AVG(price) FROM sales;

Solution

1. Step 1: Check aggregate separation

   Aggregates must be separated by commas in SELECT clause.

2. Step 2: Identify correct syntax

   SELECT SUM(price), AVG(price) FROM sales; uses commas correctly; others use AND, OR, or no separator which is invalid.

3. Final Answer:

   SELECT SUM(price), AVG(price) FROM sales; -> Option B

4. Quick Check:

   Aggregates separated by commas [OK]

Hint: Separate aggregates with commas, not AND/OR [OK]

Common Mistakes:

• Using AND or OR instead of commas
• Omitting commas between aggregates
• Writing aggregates without any separator

3. Given the table orders with column amount, what will this query return?

SELECT COUNT(*), MAX(amount), MIN(amount) FROM orders;

medium

A. Number of rows, highest amount, lowest amount

B. Sum of amounts, average amount, number of rows

C. Only the highest amount

D. Syntax error due to multiple aggregates

Solution

1. Step 1: Understand each aggregate

   COUNT(*) counts rows, MAX(amount) finds highest value, MIN(amount) finds lowest value.

2. Step 2: Combine results

   All three aggregates return one row with three summary values: count, max, and min.

3. Final Answer:

   Number of rows, highest amount, lowest amount -> Option A

4. Quick Check:

   COUNT, MAX, MIN = count, max, min [OK]

Hint: Each aggregate returns one summary value in the result [OK]

Common Mistakes:

• Confusing COUNT(*) with SUM(amount)
• Expecting multiple rows instead of one
• Thinking multiple aggregates cause syntax error

4. Identify the error in this SQL query:

SELECT SUM(price), AVG(price) FROM sales GROUP BY category;

medium

A. No error, query is correct

B. Cannot use SUM and AVG together

C. Missing GROUP BY column in SELECT clause

D. GROUP BY should be after WHERE clause

Solution

1. Step 1: Check GROUP BY usage

   When using GROUP BY category, category must appear in SELECT to show groups.

2. Step 2: Identify missing column

   Query selects only aggregates but misses category column, causing error or unexpected output.

3. Final Answer:

   Missing GROUP BY column in SELECT clause -> Option C

4. Quick Check:

   GROUP BY columns must appear in SELECT [OK]

Hint: Include GROUP BY columns in SELECT list [OK]

Common Mistakes:

• Omitting GROUP BY column in SELECT
• Thinking SUM and AVG can't be combined
• Misplacing GROUP BY clause

5. You want to find the total sales, average sales, and number of sales for each product category in a sales table with columns category and amount. Which query correctly combines these aggregates?

hard

A. SELECT category, SUM(amount) AND AVG(amount) AND COUNT(*) FROM sales GROUP BY category;

B. SELECT SUM(amount), AVG(amount), COUNT(*) FROM sales;

C. SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales;

D. SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category;

Solution

1. Step 1: Include category in SELECT and GROUP BY

   To get aggregates per category, category must be in SELECT and GROUP BY.

2. Step 2: Combine aggregates with commas

   SUM(amount), AVG(amount), COUNT(*) are combined with commas to get totals, averages, and counts.

3. Final Answer:

   SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category; -> Option D

4. Quick Check:

   GROUP BY category with aggregates = SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category; [OK]

Hint: Group by category and list aggregates with commas [OK]

Common Mistakes:

• Missing GROUP BY clause
• Using AND instead of commas between aggregates
• Not including category in SELECT when grouping