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.