CODEWITHSUNDEEP
sqlitenode-sqlite3
Hysii
Hysii

Reputation: 772

Returning count for each id in table

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!

Upvotes: 0

Views: 50

Answers (1)

isaace
isaace

Reputation: 3429

You can use COUNT with GROUP BY

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

Upvotes: 1

Related Questions