Add a column count in a MySQL query

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.

Use MySQL's COUNT() aggregate function.

Funk Forty Niner
– Funk Forty Niner

2014-10-30 04:20:22 +00:00
Commented Oct 30, 2014 at 4:20 — Funk Forty Niner
– Funk Forty Niner, Commented Oct 30, 2014 at 4:20

Chelsea Urquhart · Accepted Answer · 2014-10-30 04:26:40Z

3

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)

answered Oct 30, 2014 at 4:26

Chelsea Urquhart

1,4281 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Funk Forty Niner Over a year ago

"The above 2 answers won't work with multiple families." - I don't think yours will neither. I doubt OP has a column called familySize.

Chelsea Urquhart Over a year ago

My answer doesn't expect a column called familySize.

Funk Forty Niner Over a year ago

What do you call this then SELECT id, name, family, familySize <=?

Funk Forty Niner Over a year ago

Believe me, I have. What is it that I'm not grasping?

Chelsea Urquhart Over a year ago

JOIN (aka INNER JOIN) requires results in both tables, so when joining to itself you know for certain there are results. LEFT JOIN will return the first result even if there are no results on the subsequent query; I hope that makes sense, I'm having trouble wording it.

|

justrohu · Accepted Answer · 2014-10-30 04:23:34Z

1

You can do it like this

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

answered Oct 30, 2014 at 4:23

justrohu

5893 silver badges9 bronze badges

Comments

Rafiqul Islam · Accepted Answer · 2014-10-30 04:24:29Z

1

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

answered Oct 30, 2014 at 4:24

Rafiqul Islam

1,6561 gold badge14 silver badges25 bronze badges

1 Comment

Funk Forty Niner Over a year ago

I don't think OP has a column called familySize

Dhaval Kakkad · Accepted Answer · 2014-10-30 04:36:52Z

1

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.

answered Oct 30, 2014 at 4:36

Dhaval Kakkad

631 silver badge7 bronze badges

Collectives™ on Stack Overflow

Add a column count in a MySQL query

4 Answers 4

9 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related