7KHqkzp7

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