Returning count for each id in table

Question

Im using sqlite3 with my node.js API.

I have a DB talbe structured below:

id | colour
___|_______
1  |  blue
1  |  red
1  |  green
2  |  yellow
2  |  green
5  |  red

I want to return a count of the IDs in my table such that

1 - 3 occurences
2 - 2 occurences 
5 - 1 occurence

Is there a sql qualifier I can use count like this, or will this need to be done within the js iteself?

Any help here would be awesome!

Answer 1

You can use COUNT with GROUP BY

select id, COUNT(id) from tbl GROUP BY id