MySQL select count rows for each unique item in column

Question

It's easiest to explain by way of an example. Lets say I have a table with 3 columns - father, their offspring and their childs sex. The result table will list the fathers number of children and the split of males and females.

This is the table:

Father   Child    Sex
-----------------------
alpha    name1    m
alpha    name2    f
alpha    name3    m
alpha    name4    f
alpha    name5    f
beta     name6    m
beta     name7    m
charlie  name8    f
charlie  name9    m

Desired result:

Father   num   m    f
-----------------------
alpha    5     2    3
beta     2     2    0
charlie  2     1    1

num = number of children
m = male, f = female

When I use count it gives me the the total amount of children from all fathers and I'm not sure how to split the results into male and female. Any ideas?

Show the sql statement you used that is returning the wrong counts — AgRizzo, Feb 28 '14 at 14:02

Grijesh Chauhan · Accepted Answer · 2014-02-28T14:11:09.363

2

Try:

SELECT
    Father,
    COUNT(child) AS total_child, 
    SUM(IF(Sex = 'm', 1, 0)) AS total_m,
    SUM(IF(Sex = 'f', 1, 0)) AS total_f
FROM
    table_name
GROUP BY
    Father

edited Feb 28 '14 at 14:11

answered Feb 28 '14 at 14:05

Grijesh Chauhan

57,103
20
141
208

@RobertRozas thanks to correct typo...but yet not test. – Grijesh Chauhan Feb 28 '14 at 14:08
1

Grijesh and I go way back. He's your guy for SQL. I made [this](http://sqlfiddle.com/#!2/9be0c/3) just for fun. – Drewness Feb 28 '14 at 14:11
@Drewness thanks :) I was thinking [I know you](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events) – Grijesh Chauhan Feb 28 '14 at 14:14
@GrijeshChauhan - Oh yeah buddy! From [here](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events). :) – Drewness Feb 28 '14 at 14:15
2

Good guy @Drewness xD – Hackerman Feb 28 '14 at 14:18

score 1 · Answer 2 · answered Feb 28 '14 at 14:04

Something like this:

select distinc t.Father, 
(select count(1) from table t1 where t1.Father = t.Father) as num,
(select count(1) from table t1 where t1.Father = t.Father and Sex = 'm') as m,
(select count(1) from table t1 where t1.Father = t.Father and Sex = 'f') as f
from table t;

score 1 · Answer 3 · answered Feb 28 '14 at 14:04

1

The trick is to use SUM() around a boolean variable.

SELECT Father, COUNT(Child) as num, SUM(Sex='m') as m, SUM(Sex='f') as f
FROM table
GROUP BY Father;

answered Feb 28 '14 at 14:04

Daniel Sparing

2,163
15
20

MySQL select count rows for each unique item in column

3 Answers3