By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 t1.name AS lev1, COUNT(DISTINCT t2.name) as lev2, COUNT(DISTINCT t3.name) as lev3, COUNT(DISTINCT 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'
GROUP BY t1.name
| lev1 | lev2 | lev3 | lev4 |
|---|---|---|---|
| ELECTRONICS | 2 | 6 | 1 |