[SOLVED] Left Join same table to get count

[this.user]

lets say i have a table called members

 

idnamegroup

1e1

2f1

3g2

 

and I want to execute a query that calls the table returns a row for every member and at the same time tells me how many people are that persons group.

 

so for example

 

it would return

 

e 2

f 2

g 1

 

 

the query i have tried using does not work:

 

select  count(t.id) As tcount, member.name from member 
LEFT JOIN member AS t ON(t.group = member.group)
Group by t.group 

[Mchl]

First of all GROUP is one of MySQL reserved words, so it shouldn't be used as column name.

 

Then:

SELECT
  t1.id,
  t1.name,
  t1.`group`,
  t2.groupCount
FROM
  member AS t1
LEFT JOIN (
  SELECT `group`, COUNT(*) AS groupCount FROM member GROUP BY `group`
) AS t2
ON
t1.`group` = t2.`group`

[this.user]

Thx mchl