MySQL: Use the column's value on the same row for counting -

how use value other's column can create column sum of same type.

e.g.

+---------+------+ |  name   | team | +---------+------+ | michael | red  | | lebron  | blue | | ben     | red  | | tiger   | red  | | john    | blue | +---------+------+ 

output:

+---------+------+----------------+ |  name   | team | member_counter | +---------+------+----------------+ | michael | red  |              3 | | lebron  | blue |              2 | | ben     | red  |              3 | | tiger   | red  |              3 | | john    | blue |              2 | +---------+------+----------------+ 

as can see there 3 reds , 2 blues. there anyway can use value of previous columns? count(team=previous column)? member_counter?

you should join sub select of teams

select name, team, teams.count   names  join (select team, count(1) count names group team) teams using (team); 

sqlfiddle