GROUP BY single and multiple columns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

GROUP BY with a single column

Consider a table sales with columns region and amount. What is the output of this query?

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

PostgreSQL

CREATE TABLE sales(region TEXT, amount INT);
INSERT INTO sales VALUES
('East', 100), ('West', 200), ('East', 150), ('North', 50), ('West', 100);

A [{"region": "East", "sum": 100}, {"region": "West", "sum": 200}, {"region": "North", "sum": 50}]

B [{"region": "East", "sum": 250}, {"region": "West", "sum": 300}]

C [{"region": "East", "sum": 250}, {"region": "North", "sum": 50}, {"region": "West", "sum": 300}]

D [{"region": "East", "sum": 250}, {"region": "North", "sum": 50}, {"region": "West", "sum": 200}]

💡 Hint

GROUP BY groups rows by the unique values in the region column, then SUM adds amounts per group.

The query groups all rows by region. For each region, it sums the amount values. East has 100 + 150 = 250, West has 200 + 100 = 300, North has 50.

❓ query_result

intermediate

GROUP BY with multiple columns

Given a table orders with columns customer_id, product_id, and quantity, what is the output of this query?

SELECT customer_id, product_id, SUM(quantity) FROM orders GROUP BY customer_id, product_id ORDER BY customer_id, product_id;

PostgreSQL

CREATE TABLE orders(customer_id INT, product_id INT, quantity INT);
INSERT INTO orders VALUES
(1, 101, 2), (1, 102, 3), (2, 101, 1), (1, 101, 4), (2, 103, 5);

A [{"customer_id": 1, "product_id": 101, "sum": 6}, {"customer_id": 1, "product_id": 102, "sum": 3}, {"customer_id": 2, "product_id": 101, "sum": 1}, {"customer_id": 2, "product_id": 103, "sum": 5}]

B [{"customer_id": 1, "product_id": 101, "sum": 6}, {"customer_id": 1, "product_id": 102, "sum": 3}, {"customer_id": 2, "product_id": 101, "sum": 1}]

C [{"customer_id": 1, "product_id": 101, "sum": 4}, {"customer_id": 1, "product_id": 102, "sum": 3}, {"customer_id": 2, "product_id": 101, "sum": 1}, {"customer_id": 2, "product_id": 103, "sum": 5}]

D [{"customer_id": 1, "product_id": 101, "sum": 6}, {"customer_id": 1, "product_id": 102, "sum": 3}, {"customer_id": 2, "product_id": 103, "sum": 5}]

💡 Hint

GROUP BY with two columns groups rows by unique pairs of customer_id and product_id.

The query sums quantity for each unique pair of customer_id and product_id. Customer 1 ordered product 101 twice (2 + 4 = 6), product 102 once (3). Customer 2 ordered product 101 once (1) and product 103 once (5).

📝 Syntax

advanced

GROUP BY with aggregate and non-aggregated columns

Which of the following queries will cause a syntax error in PostgreSQL?

SELECT department, employee_name, COUNT(*) FROM employees GROUP BY department;

PostgreSQL

CREATE TABLE employees(department TEXT, employee_name TEXT);

A The query runs but returns NULL for employee_name.

B The query runs correctly and returns counts per department with employee names.

C The query runs but returns random employee_name values per department.

D The query will cause a syntax error because employee_name is not in GROUP BY or aggregated.

💡 Hint

In PostgreSQL, all selected columns must be either aggregated or included in GROUP BY.

The query selects employee_name which is neither aggregated nor in the GROUP BY clause. PostgreSQL requires all non-aggregated columns to be in GROUP BY, so this causes a syntax error.

❓ optimization

advanced

Optimizing GROUP BY with multiple columns

You have a large table transactions with columns user_id, category, and amount. Which index will best optimize this query?

SELECT user_id, category, SUM(amount) FROM transactions GROUP BY user_id, category;

A CREATE INDEX idx_category_user ON transactions(category, user_id);

B CREATE INDEX idx_user_category ON transactions(user_id, category);

C CREATE INDEX idx_amount ON transactions(amount);

D CREATE INDEX idx_user ON transactions(user_id);

💡 Hint

Indexes that match the GROUP BY columns in order help aggregation queries.

An index on (user_id, category) matches the GROUP BY columns and their order, helping PostgreSQL efficiently group and aggregate data.

🧠 Conceptual

expert

Understanding GROUP BY with NULL values

Given a table products with columns category and price, what will this query return?

SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY category;

Assume some rows have category as NULL.

PostgreSQL

CREATE TABLE products(category TEXT, price INT);
INSERT INTO products VALUES
('Books', 10), (NULL, 20), ('Books', 15), (NULL, 30), ('Games', 25);

A [{"category": null, "count": 2}, {"category": "Books", "count": 2}, {"category": "Games", "count": 1}]

B [{"category": "Books", "count": 2}, {"category": "Games", "count": 1}]

C [{"category": "Books", "count": 2}, {"category": "Games", "count": 1}, {"category": null, "count": 0}]

D [{"category": null, "count": 0}, {"category": "Books", "count": 2}, {"category": "Games", "count": 1}]

💡 Hint

GROUP BY treats NULL as a distinct group.

Rows with NULL in category form their own group. So the count for NULL category is 2, Books is 2, Games is 1.

Practice

1. What does the GROUP BY clause do in a PostgreSQL query?

easy

A. It updates values in the specified columns.

B. It sorts the rows in ascending order.

C. It deletes duplicate rows from the table.

D. It groups rows that have the same values in specified columns.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows that share the same value in one or more columns into groups.

2. Step 2: Differentiate from other clauses

   Unlike ORDER BY which sorts rows, GROUP BY organizes rows into groups for aggregation.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option D

4. Quick Check:

   GROUP BY = groups rows by column values [OK]

Hint: GROUP BY collects rows sharing column values into groups [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY deletes duplicates
• Assuming GROUP BY updates data

2. Which of the following is the correct syntax to group data by two columns, category and region?

easy

A. SELECT category, region, COUNT(*) FROM sales GROUP BY category, region;

B. SELECT category, region, COUNT(*) FROM sales GROUP BY category region;

C. SELECT category, region, COUNT(*) FROM sales GROUP BY (category, region);

D. SELECT category, region, COUNT(*) FROM sales GROUP category, region;

Solution

1. Step 1: Check correct GROUP BY syntax

   In PostgreSQL, multiple columns are separated by commas in GROUP BY without parentheses.

2. Step 2: Validate each option

   SELECT category, region, COUNT(*) FROM sales GROUP BY category, region; uses commas correctly; SELECT category, region, COUNT(*) FROM sales GROUP BY category region; misses comma; SELECT category, region, COUNT(*) FROM sales GROUP BY (category, region); uses parentheses incorrectly; SELECT category, region, COUNT(*) FROM sales GROUP category, region; misses BY keyword.

3. Final Answer:

   SELECT category, region, COUNT(*) FROM sales GROUP BY category, region; -> Option A

4. Quick Check:

   GROUP BY columns separated by commas [OK]

Hint: Separate multiple columns with commas in GROUP BY [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Forgetting the BY keyword

3. Given the table orders with columns customer_id, product_id, and quantity, what will this query return?

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

medium

A. Total quantity ordered by each customer.

B. Total quantity ordered by each product.

C. Total quantity ordered by each customer and product.

D. Syntax error due to missing GROUP BY columns.

Solution

1. Step 1: Analyze the SELECT and GROUP BY columns

   The query groups rows by customer_id and sums quantity for each group.

2. Step 2: Understand the aggregation result

   SUM(quantity) calculates total quantity per customer_id, ignoring product_id.

3. Final Answer:

   Total quantity ordered by each customer. -> Option A

4. Quick Check:

   GROUP BY customer_id sums quantity per customer [OK]

Hint: GROUP BY column matches SELECT non-aggregates [OK]

Common Mistakes:

• Assuming product_id affects grouping
• Thinking query returns per product totals
• Confusing aggregation with filtering

4. Identify the error in this query:

SELECT product_id, region, COUNT(*) FROM sales GROUP BY product_id;

medium

A. COUNT(*) cannot be used with GROUP BY.

B. Missing region in GROUP BY causes error.

C. product_id cannot be grouped alone.

D. Syntax error due to missing FROM clause.

Solution

1. Step 1: Check SELECT columns vs GROUP BY columns

   SELECT includes product_id and region, but GROUP BY only has product_id.

2. Step 2: Understand PostgreSQL grouping rules

   All non-aggregated columns in SELECT must appear in GROUP BY; region is missing.

3. Final Answer:

   Missing region in GROUP BY causes error. -> Option B

4. Quick Check:

   All SELECT non-aggregates must be in GROUP BY [OK]

Hint: Include all non-aggregated SELECT columns in GROUP BY [OK]

Common Mistakes:

• Forgetting to add all columns to GROUP BY
• Thinking COUNT(*) disallows grouping
• Ignoring SELECT and GROUP BY mismatch

5. You want to find the total sales quantity for each product in each region from the sales table with columns product_id, region, and quantity. Which query correctly groups and sums the data?

hard

A. SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id;

B. SELECT product_id, region, SUM(quantity) FROM sales GROUP BY product_id;

C. SELECT product_id, region, SUM(quantity) FROM sales GROUP BY product_id, region;

D. SELECT product_id, region, SUM(quantity) FROM sales;

Solution

1. Step 1: Identify grouping needs

   We want totals per product and per region, so both columns must be grouped.

2. Step 2: Check each option's GROUP BY clause

   SELECT product_id, region, SUM(quantity) FROM sales GROUP BY product_id, region; groups by product_id and region, matching SELECT columns; others miss region or GROUP BY entirely.

3. Final Answer:

   SELECT product_id, region, SUM(quantity) FROM sales GROUP BY product_id, region; -> Option C

4. Quick Check:

   Group by all non-aggregated columns for correct totals [OK]

Hint: Group by all selected non-aggregated columns [OK]

Common Mistakes:

• Grouping by only one column when selecting multiple
• Omitting GROUP BY causing aggregation errors
• Selecting columns without aggregation or grouping