I have a table speaking to an envelope structure, The branch field speaks to a bundle structure (or organizer structure) containing things. (for this situation, there is just a single thing for every record, however for my situation there will be numerous things at each level of profundity ( think essentially about a windows traveler structure containing things) ..

I require a question that will check what number of every thing are inside the branches of the tree structure. nonetheless I have to tally all things inside a specific profundity and lower, i.e. where the profundity is more noteworthy than 4, get a check of all things of a particular kind - I will require the tallies of all organizers at level 4 with the tallies of things at that level. https://goo.gl/frpdYV

im occupied with a cte inquiry, however I think im heading in the wrong bearing.

DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  ,branch varchar(100)
  ,depth int
  ,item varchar(20)
  )
INSERT  INTO @tbl
        ( Id, ParentId,branch, depth,item )
        VALUES  (1, NULL,   '1',0,Null),
                (2, 1,  '1,2',1,NULL),
                (3, 1,  '1,2,3',2,NULL),
                (4, 3,  '1,2,3,4',3,NULL),
                (5, 4,  '1,2,3,4,5',4,NULL),
                (6, 5,  '1,2,3,4,5,6',5,'car'),
                (7, 6,  '1,2,3,4,5,6,7',6,'bus'),
                (8, 7,  '1,2,3,4,5,6,7,8',7,'truck'),
                (9, 8,  '1,2,3,4,5,6,7,8,9',8,'car'),
                (10,8,  '1,2,3,4,5,6,7,8,10',9,'bike'),
                (11,5,  '1,3,4,5,11',4,'car'),
                (12,5,  '1,3,4,5,12',4,'truck'),
                (13,4,  '1,2,3,4,13',4,'truck'),
                (14,8,  '1,2,3,4,5,6,7,8,14',8,'bike'),
                (15,8,  '1,2,3,4,5,6,7,8,15',8,'bus');

The data is probably incorrect. but essentially the result (if you only wanted the count of cars) would take all items (and their children) and count the cars.

i.e.

branch     car
'1,2,3,4'   3
'1,3,4,5'   0