SolveWithSQL

Difficulty: Beginner
Estimated time: 10–15 minutes
SQL concepts: GROUP BY, aggregation, summarizing data
Goal: Learn how to group rows and calculate summary values.

The Scenario

A product manager asks:

“How many products do we have per category?”

Instead of counting all products together, you now need to group products by category and calculate how many products fall into each one.

Database Table

products

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
category	TEXT	Product category
price	DECIMAL	Product price
stock_qty	INTEGER	Units in stock
created_at	TIMESTAMP	When the product was created

Sample Data (for context)

product_id	name	category	price
101	Wireless Mouse	Accessories	24.99
102	Mechanical Keyboard	Accessories	89.00
103	27-inch Monitor	Displays	229.99
104	USB-C Hub	Accessories	34.50
105	Laptop Stand	Workspace	39.99

Your Task

Write a SQL query that returns:

• Each category
• The number of products in that category

Expected Output

category	total_products
Accessories	3
Displays	1
Workspace	1

(Order does not matter unless you add ORDER BY.)

Constraints

• Use GROUP BY
• Use COUNT() to calculate totals
• Return exactly two columns:
  • category
  • total_products

Explanation

• GROUP BY category groups rows with the same category together.
• COUNT(*) counts how many rows exist in each group.
• The result is one row per category, with a calculated total.

Common Mistakes

1. Forgetting to group by non-aggregated columns
   SELECT category, price, COUNT(*) FROM products GROUP BY category; -- ❌
2. Using WHERE instead of GROUP BY
3. Confusing GROUP BY with DISTINCT

Optional Extension (Mini Bonus)

Try answering:

1. How many products are in each category priced above 50?
2. Which category has the most products?
3. Sort categories by product count (highest first).

Next Challenge

Beginner Challenge #7: Filtering Groups with HAVING

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.