3EiKKQCM

1CREATE TABLE IF NOT EXISTS `test` (
2  `id` int(10) NOT NULL AUTO_INCREMENT,
3  `parent_id` int(10) DEFAULT NULL,
4  `text` text,
5  PRIMARY KEY (`id`)
6) ENGINE=InnoDB DEFAULT CHARSET=latin1;
7
8
9INSERT INTO `test` (`id`, `parent_id`, `text`) VALUES
10	(1, NULL, 'Main'),
11	(2, 1, 'Sub 1'),
12	(3, 1, 'Sub 2'),
13	(4, NULL, 'Main 2'),
14	(5, NULL, 'Main 3');
15
16
17/* the query: */
18select 
19if(parent_id is null,id,parent_id) as pid, count(1)
20from `test` 
21group by if(parent_id is null,id,parent_id)