SQL: How to get the count of each distinct value in a column?
I have a SQL table called "posts" that looks like this:
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?
As an example, such a query might return a symbolic array such as this: (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.
Answer
SELECT
category,
COUNT(*) AS `num`
FROM
posts
GROUP BY
category