CODEWITHSUNDEEP
phpmysql
user3822370
user3822370

Reputation: 647

Add a column count in a MySQL query

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.

Upvotes: 2

Views: 897

Answers (4)

Dhaval Kakkad
Dhaval Kakkad

Reputation: 63

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.

Upvotes: 1

Chelsea Urquhart
Chelsea Urquhart

Reputation: 1428

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)

Upvotes: 3

Rafiqul Islam
Rafiqul Islam

Reputation: 1646

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

Upvotes: 1

justrohu
justrohu

Reputation: 589

You can do it like this

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

Upvotes: 1

Related Questions