在MySQL查询中添加列计数

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 1

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)