在MySQL查询中添加列计数

I am trying to achieve a strange query. I have a table like this:

table People

-----------------------
| id | name | family |
+----+------+--------+
| 1  |  Bob |  Smith |
+----+------+--------+
| 2  |  Joe | Smith  |
----------------------

I want to return an assoc array like this

id : 1
name : bob
family : smith
familySize : 2

So something like this

"SELECT id, name, family, familySize FROM People"

How can I get the familySize in there? Keep in mind my query may have many families and I want them all to be returned.

Thank you.

The above 2 answers won't work with multiple families.

You can do something like this:

SELECT id, name, family, familySize FROM People p1
JOIN (SELECT COUNT(*) as familySize, family FROM People GROUP BY family) p2
USING(family)

You can do it like this

SELECT id, name, family, count(id) as familySize FROM People

     "SELECT id, name, family, count(family) as familySize FROM People group by   
      family"

I would like to mention here that what if two or three families have the same surname "Smith". It won't give you the desired results.

What I suggest you is while inserting the data in the table assign a family head and all the family members have a field called family_ID has primary key as value of family_head.

This will give you accurate results with 1000's of people. I have made a family portal has over 12k members now and it works fine for me.

Hope it helps.