Why aggregation matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the total sales amount for each product?

Given a sales table with columns product_id and amount, which query correctly calculates the total sales amount per product?

PostgreSQL

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

A SELECT product_id, COUNT(amount) FROM sales;

B SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;

C SELECT product_id, amount FROM sales;

D SELECT product_id, SUM(amount) FROM sales;

💡 Hint

Think about how to group rows to get totals per product.

Option B groups sales by product and sums the amounts, giving total sales per product. Option B just lists rows without aggregation. Option B counts sales per product but does not sum amounts. Option B tries to sum without grouping, causing an error.

🧠 Conceptual

intermediate

Why use aggregation functions in databases?

Which statement best explains why aggregation functions like SUM, COUNT, and AVG are important in databases?

A They allow combining multiple rows into a single summary value, helping to analyze data efficiently.

B They create new tables automatically based on conditions.

C They speed up data entry by automating inserts.

D They delete duplicate rows from tables.

💡 Hint

Think about what aggregation functions do with many rows of data.

Aggregation functions summarize multiple rows into one value, which helps in understanding overall trends or totals. The other options describe unrelated database operations.

📝 Syntax

advanced

Identify the syntax error in this aggregation query

What error does this query produce?

SELECT department, AVG(salary) FROM employees WHERE department = 'Sales';

A RuntimeError: invalid column name 'salary'

B SyntaxError: missing GROUP BY clause when using aggregation with non-aggregated columns

C No error, query runs correctly

D SyntaxError: WHERE clause cannot be used with aggregation functions

💡 Hint

Check if all non-aggregated columns are grouped.

The query filters rows to only those where department = 'Sales', so the result is a single department. Therefore, no GROUP BY is needed and the query runs correctly. Option C is incorrect because the WHERE clause filters before aggregation. Option C is wrong because salary exists. Option C is false; WHERE can be used before aggregation.

❓ optimization

advanced

Optimize aggregation query for large datasets

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

A Remove the GROUP BY clause to speed up the query

B Run the query without any indexes or optimizations

C Use SELECT * instead of specifying columns

D Create an index on the <code>region</code> column before running <code>SELECT region, SUM(amount) FROM orders GROUP BY region;</code>

💡 Hint

Indexes help queries filter and group data faster.

Indexing the grouping column helps the database quickly locate rows per region, speeding aggregation. Removing GROUP BY or using SELECT * does not help and may slow down. Running without indexes is inefficient.

🔧 Debug

expert

Why does this aggregation query return fewer rows than expected?

Given the query:

SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;

Why might some categories be missing from the result?

A Because the HAVING clause filters out categories with 5 or fewer products, so only categories with more than 5 appear.

B Because GROUP BY removes duplicate categories entirely.

C Because COUNT(*) counts only non-null categories, so null categories are excluded.

D Because the query syntax is invalid and returns no rows.

💡 Hint

Consider what HAVING does after grouping.

HAVING filters groups after aggregation. It excludes categories with 5 or fewer products, so only categories with more than 5 products appear. GROUP BY does not remove categories, it groups them. COUNT(*) counts all rows in the group, including nulls in other columns. The syntax is valid.