How can i do this query in sql? count number of category

Question

my table is:

name | category |

name01 category01
name02 category01
name03 category02
name04 category02
name05 category02
name06 category02
name07 category02
..... I would like to count for each category the number of name

the output i woulk like is something like this (by the example above):

category | num

category01 2
category02 5
...

thanks to all that would to help me...

Answer 1

SELECT category, COUNT(*) FROM table GROUP BY category

Altough count could be a bit slow on very big tables, so if you run a very big service or operate on a lot of data you might consider cache the resoult or denormalize the table. (However, it these techiques need a really big table)

Answer 2

this is very basic, but using a GROUP BY statement should give the desired result. i.e:

SELECT category, COUNT(*) FROM table GROUP BY category

Answer 3

Always use COUNT(1) for faster execution

Answer 4

Here is a hypothetical question.

Say that category was indexed. Would it be any faster to write the query like this

select category, count(category) from table group by category