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.