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.

Practice

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

easy

A. To summarize multiple rows into a single value

B. To delete duplicate rows from a table

C. To join two tables together

D. To create new tables from existing ones

Solution

1. Step 1: Understand aggregation functions

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

2. Step 2: Identify their purpose

   They help to reduce data by summarizing, not by deleting or joining tables.

3. Final Answer:

   To summarize multiple rows into a single value -> Option A

4. Quick Check:

   Aggregation = Summary [OK]

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

Common Mistakes:

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

2. Which of the following is the correct syntax to count rows in a table named sales?

easy

A. SELECT COUNT ALL sales;

B. SELECT COUNT(*) FROM sales;

C. SELECT COUNT(sales) FROM *;

D. SELECT COUNT FROM sales();

Solution

1. Step 1: Recall correct COUNT syntax

   The correct syntax to count all rows is SELECT COUNT(*) FROM table_name;.

2. Step 2: Check each option

   SELECT COUNT(*) FROM sales; matches the correct syntax. Others have syntax errors or wrong order.

3. Final Answer:

   SELECT COUNT(*) FROM sales; -> Option B

4. Quick Check:

   COUNT(*) syntax = SELECT COUNT(*) FROM sales; [OK]

Hint: COUNT(*) counts all rows, use parentheses and star [OK]

Common Mistakes:

• Omitting parentheses after COUNT
• Using COUNT without * or column
• Wrong order of keywords

3. Given the table orders with columns customer_id and amount, what does this query return?

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

medium

A. Total amount spent by each customer

B. List of all orders with amounts

C. Total amount spent by all customers combined

D. Number of orders per customer

Solution

1. Step 1: Understand GROUP BY usage

   GROUP BY customer_id groups rows by each customer.

2. Step 2: Understand SUM(amount)

   SUM(amount) adds all amounts per customer group.

3. Final Answer:

   Total amount spent by each customer -> Option A

4. Quick Check:

   GROUP BY + SUM = total per group [OK]

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

Common Mistakes:

• Thinking SUM returns total for all rows without grouping
• Confusing SUM with COUNT
• Ignoring GROUP BY effect

4. What is wrong with this query?

SELECT customer_id, COUNT(*) FROM orders;

medium

A. customer_id must be inside COUNT()

B. COUNT(*) cannot be used without WHERE

C. Missing GROUP BY clause for customer_id

D. SELECT must have ORDER BY with COUNT

Solution

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

   customer_id is selected but not aggregated or grouped.

2. Step 2: Identify missing GROUP BY

   To use COUNT(*) with customer_id, GROUP BY customer_id is required.

3. Final Answer:

   Missing GROUP BY clause for customer_id -> Option C

4. Quick Check:

   Non-aggregated columns need GROUP BY [OK]

Hint: Use GROUP BY when mixing columns with aggregation [OK]

Common Mistakes:

• Forgetting GROUP BY when selecting non-aggregated columns
• Thinking COUNT needs WHERE clause
• Assuming ORDER BY is mandatory with aggregation

5. You want to find the average order amount per customer but only for customers who placed more than 3 orders. Which query correctly achieves this?

hard

A. SELECT customer_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id;

B. SELECT customer_id, AVG(amount) FROM orders HAVING COUNT(*) > 3 GROUP BY customer_id;

C. SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id WHERE COUNT(*) > 3;

D. SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;

Solution

1. Step 1: Understand filtering groups with HAVING

   HAVING filters groups after aggregation; WHERE filters rows before aggregation.

2. Step 2: Check query order and clauses

   SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; correctly uses GROUP BY then HAVING COUNT(*) > 3 to filter customers with more than 3 orders.

3. Final Answer:

   SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; -> Option D

4. Quick Check:

   Use HAVING to filter groups after GROUP BY [OK]

Hint: Use HAVING to filter groups, not WHERE [OK]

Common Mistakes:

• Using WHERE to filter aggregated counts
• Placing HAVING before GROUP BY
• Syntax errors mixing WHERE and HAVING