On 05/26/2016 11:59 AM, Jim Wang wrote:
hi all
a table as follow:
id score
2 10
3 20
5 10
3 20
2 30
2 30
how could I select the table as follow and the count can tell me: the id 2
hava 3,the id 3 have 2 the id 5 have 1.
count id score
3 2 10
2 3 20
1 5 10
2 3 20
3 2 30
3 2 30
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Another solution
select b.count, a.id, a.score from t as a join (select id, count(score)
as count from t group by id) as b on a.id=b.id;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
