How to return multi-count of specific values

Question

I have a table like this:

id|category

where category is an integer between 0 and 3. How do I count how many rows are in each category. Is it possible in a single query?

F.x. if I have these rows:

a|3
b|1
d|1
f|0
g|2

the result shoud be:

(1,2,1,1)

ie. 1 in category = 0, 2 in category = 1 etc.

Answer 1

This will get you a row for each category.

SELECT category, COUNT(*) as catcount
    FROM YourTable
    GROUP BY category

To get output in the exact format you specified (1 row, 4 columns):

SELECT SUM(CASE WHEN category = 0 THEN 1 ELSE 0 END) AS cat0count,
       SUM(CASE WHEN category = 1 THEN 1 ELSE 0 END) AS cat1count,
       SUM(CASE WHEN category = 2 THEN 1 ELSE 0 END) AS cat2count,
       SUM(CASE WHEN category = 3 THEN 1 ELSE 0 END) AS cat3count
    FROM YourTable