Question: 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.

Answer: 
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)

```
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.