SQL分组统计问题：如何按类别统计商品数量？

Got it, let's sort out this SQL problem for you—you're trying to count how many products are in each category, but your current query is just listing every product instead. Let's break down what's going wrong and fix it.

What's Wrong With Your Original Query? #

Your current SQL is:

SELECT items.id, items.category, COUNT(*) FROM items GROUP BY items.id, items.category

The issue here is including items.id in your GROUP BY clause. Since every product has a unique id, this forces the database to create a separate group for each individual product. Each group only has one item, so COUNT(*) will always return 1—hence why you're seeing every product listed instead of aggregated category counts.

The Correct Query to Count Products Per Category #

To get the count of products in each category, you only need to group by the category field, and don't select the id (since it doesn't make sense to show a single id for a group of products). Here's the fixed query:

SELECT items.category, COUNT(*) AS product_count
FROM items
GROUP BY items.category;

Let's break this down:

• GROUP BY items.category: Clusters all products that share the same category into a single group
• COUNT(*): Counts the number of products in each group
• AS product_count: Gives the count column a clear, readable name

Example Output #

If your items table looks like this:

Running the fixed query will give you this result (exactly what you're looking for):

Bonus: Handling Null Categories #

If some products don't have a category (NULL value), you can use the COALESCE function to replace NULL with a friendly label like "未分类":

SELECT 
  COALESCE(items.category, '未分类') AS category, 
  COUNT(*) AS product_count
FROM items
GROUP BY items.category;

This will make your results more polished, grouping all uncategorized products under one clear label.

内容的提问来源于stack exchange，提问作者Omar Krichen