Product count of a subtree SQL nested set

Question

See http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Under the heading "Aggregate Functions in a Nested Set"

I'm trying to work out a query similar to the example given, except I want it to work at a sub-tree level, so if I queried for MP3 players I would get a result set like;

|NAME          |COUNT|
----------------------
|MP3 PLAYERS   |  2  | // 2 because 1 at this level and 1 at child level  
|FLASH PLAYERS |  1  |

Answer 1

Assuming the self referencing table tree_node is created as follows:

CREATE TABLE tree_node
(
  id serial NOT NULL,
  parent integer,
  "desc" text,
  l integer,
  r integer,
  CONSTRAINT tree_node_pkey PRIMARY KEY (id)
);

The counts can be retrieved with the following SQL:

select count(*), p.id, p.desc from tree_node c, tree_node p
where c.l<=p.r
and c.l>=p.l
group by p.id, p.desc;