title: Count of node of Hierarchical Data in MySQL
body: <p>i got a solution from link below. this works very fine, but I want to count of items at different levels instead of names. anybody can help how it is possible.
  <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/</a></p>
  <pre><code>CREATE TABLE category(
          category_id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(20) NOT NULL,
          parent INT DEFAULT NULL
     );
  
  INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
          (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
          (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
  
  SELECT * FROM category ORDER BY category_id;
  +-------------+----------------------+--------+
  | category_id | name                 | parent |
  +-------------+----------------------+--------+
  |           1 | ELECTRONICS          |   NULL |
  |           2 | TELEVISIONS          |      1 |
  |           3 | TUBE                 |      2 |
  |           4 | LCD                  |      2 |
  |           5 | PLASMA               |      2 |
  |           6 | PORTABLE ELECTRONICS |      1 |
  |           7 | MP3 PLAYERS          |      6 |
  |           8 | FLASH                |      7 |
  |           9 | CD PLAYERS           |      6 |
  |          10 | 2 WAY RADIOS         |      6 |
  +-------------+----------------------+--------+
  10 rows in set (0.00 sec)
  </code></pre>
  <p>following query on above table give result:-</p>
  <pre><code>SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
  FROM category AS t1
  LEFT JOIN category AS t2 ON t2.parent = t1.category_id
  LEFT JOIN category AS t3 ON t3.parent = t2.category_id
  LEFT JOIN category AS t4 ON t4.parent = t3.category_id
  WHERE t1.name = 'ELECTRONICS';
  
  Result:-
  
  +-------------+----------------------+--------------+-------+
  | lev1        | lev2                 | lev3         | lev4  |
  +-------------+----------------------+--------------+-------+
  | ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
  | ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
  | ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
  | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
  | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
  | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
  +-------------+----------------------+--------------+-------+
  6 rows in set (0.00 sec)
  </code></pre>
  <p>here i need to modify query to count of items at diffirent levels, instead of their names.
  for example result from above data</p>
  <pre><code>+-------------+----------------------+--------------+-------+
  | lev1        | lev2                 | lev3         | lev4  |
  +-------------+----------------------+--------------+-------+
  | ELECTRONICS | 2                    | 6            | 1     |
  </code></pre>