SQL:如何获取列中每个不同值的计数?

I have a SQL table called "posts" that looks like this:

我有一个SQL表，叫做“posts”，它看起来是这样的:

id | category
-----------------------
1  | 3
2  | 1
3  | 4
4  | 2
5  | 1
6  | 1
7  | 2

Each category number corresponds to a category. How would I go about counting the number of times each category appears on a post all in one sql query?

每个类别编号对应一个类别。如何计算每个类别在一个sql查询中出现的次数?

As an example, such a query might return a symbolic array such as this: (1:3, 2:2, 3:1, 4:1)

例如，这样的查询可能返回一个象这样的符号数组:(1:3,2:2,3:1,4:1)

---

My current method is to use queries for each possible category, such as: SELECT COUNT(*) AS num FROM posts WHERE category=#, and then combine the return values into a final array. However, I'm looking for a solution that uses only one query.

我目前的方法是对每个可能的类别使用查询，例如:从category=#的post中选择COUNT(*)作为num，然后将返回值合并到最终的数组中。但是，我正在寻找一个只使用一个查询的解决方案。

1 个解决方案

#1

SELECT
  category,
  COUNT(*) AS `num`
FROM
  posts
GROUP BY
  category