SolveWithSQL

Difficulty: Beginner
Estimated time: 15–20 minutes
SQL concepts: COUNT(), GROUP BY, aggregation per category
Goal: Count rows per group, not just across the whole table.

The Challenge

The product manager asks:

“How many products do we have in each category?”

This question introduces a key shift in SQL thinking:

• You’re no longer counting one number
• You’re counting multiple totals, one per group

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

Sample Data

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

Your Task

Write a SQL query that returns:

• Each product category
• The number of products in that category

Expected Output

category	count
Accessories	4
Displays	1
Workspace	1

Constraints

• Use COUNT()
• Use GROUP BY
• Do not use SELECT *

Hint (Optional)

When using GROUP BY:

• Every column in SELECT must either:
  • Be inside an aggregate function (COUNT, SUM, etc.)
  • Or appear in the GROUP BY clause

Explanation

• GROUP BY category splits the table into groups
• COUNT(*) counts rows within each group
• The query returns one row per category

This pattern is fundamental for:

• Dashboards
• Reports
• KPIs
• Business summaries

Common Mistakes

1. Forgetting GROUP BY SELECT category, COUNT(*) FROM products; → Invalid SQL.
2. Selecting non-grouped columns
3. Trying to filter groups using WHERE
   (That’s coming next 👀)

Optional Extension (Mini Bonus)

Try answering these:

1. How many products are in each category costing more than 50?
2. How many products are in each category priced between 30 and 100?
3. Sort the result by the count (highest first)

Why this challenge matters

This is the moment learners realize:

SQL can answer “per-category” questions in a single query.

Once GROUP BY clicks, SQL stops feeling flat and starts feeling powerful.