Re: Count Hierarchical Data(Binary Tree) in MySQL?

[Peter Brawley]

A leaf is a node that has no child nodes. Your 14 &c example counts are child node counts, not leaf node counts. To retrieve those counts, try something like ...

SELECT parent.id, Count(distinct child.id) - 1 AS 'Child Nodes'
FROM tbl parent
JOIN tbl child ON child.left_id BETWEEN parent.left_id AND parent right_id
GROUP BY parent.id;

PB
http://www.artfulsoftware.com