MySql find how many children a parent has

Question

I'm trying to find how many children a parent has, from the data below. Here level is the level and parent is the id of the row it's linked to. I'm trying to get data outputted, arranged by level and the number of children.

I pieced the below sql, but this doesn't really return results the way I want:

select id, level, count(parent) as p, country 
  from stats group by level order by level asc, p desc;

Can you help?

Table Data

"id"    "level" "parent"    "country"
"1"     "1"     "0"         "US"
"2"     "2"     "1"         "US"
"3"     "2"     "1"         "US"
"4"     "2"     "1"         "US"
"5"     "2"     "1"         "US"

"6"     "3"     "2"         "US"
"7"     "3"     "2"         "US"

"8"     "1"     "0"         "US"

"9"     "2"     "8"         "US"
"10"    "2"     "8"         "US"
"11"    "2"     "8"         "US"

"12"    "3"     "9"         "US"
"13"    "3"     "9"         "US"
"14"    "3"     "9"         "US"

"15"    "3"     "10"        "US"
"16"    "3"     "10"        "US"
"17"    "3"     "10"        "US"
"18"    "3"     "10"        "US"

Desired Output

"id"    "type"  "country"   "Count"
"1"     "1"     "US"        "4"
"8"     "1"     "US"        "3"
"10"    "2"     "US"        "4"
"9"     "2"     "US"        "3"
"2"     "2"     "US"        "2"

This looks similiar, you can find something helpful: http://stackoverflow.com/questions/7682781/mysql-count-all-children-no-matter-how-many-there-are — Kuzgun, Sep 27 '13 at 14:18

score 2 · Answer 1 · answered Sep 27 '13 at 14:33

2

Try this:

SELECT s.id, s.level, s.country, x.cnt 
FROM stats s, 
(SELECT DISTINCT(parent), COUNT(*) AS cnt 
FROM stats GROUP BY parent) x 
WHERE s.id = x.parent ORDER BY s.parent;

This way, it's ordered to show the higher from the hierarchy first.

answered Sep 27 '13 at 14:33

Minoru

1,680
3
20
44

Taemyr · Answer 2 · 2013-09-30T07:41:50.867

0

Get a count grouped by parents and join that back to the list to get the other details:

select idt.id as id, idt.level as type, idt.country as country, idc.ct as Count
from stats idt
inner join
(
  select parent, count(id) as ct
  from stats
  group by parent
) idc
on idt.id=idc.parent
order by level asc

edited Sep 30 '13 at 07:41

answered Sep 27 '13 at 14:32

Taemyr

3,407
16
26

What is idc.ct in your reply? I keep getting `Unknown column 'idc.ct' in 'field list'` – Norman Sep 27 '13 at 14:55
fixed. Missing an alias. – Taemyr Sep 30 '13 at 07:42

score 0 · Answer 3 · answered Sep 27 '13 at 14:33

It could be something like this - add your desired columns in SELECT part:

SELECT
  s.id,
  IF (p.number IS NULL,0,p.number) AS number
  FROM stats s
    LEFT JOIN
    (
      SELECT
        parent,
        COUNT(*) AS number
        FROM stats
        GROUP BY parent
        WHERE parent!=0
    ) p
    ON s.id=p.parent
  ORDER BY s.level ASC, p.number DESC

MySql find how many children a parent has

3 Answers3