Recursive CTE to find Total for all children

Question

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part.

Meaning if I search for 'Rivet', I want to know the total count at each level within the assembly, not just the direct children count.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Currently, I can get the direct parents, but want to know how to extend my CTE to allow me to roll this information upward.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i 
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

Creation script

CREATE TABLE [dbo].[Instances] ( 
  [InstanceID] NVARCHAR (50) NOT NULL, 
  [Part] NVARCHAR (50) NOT NULL, 
  [ParentID] NVARCHAR (50) NOT NULL, );



INSERT INTO Instances 
Values 
  (1, 'Assembly', 0), 
  (50, 'Rivet', 1), 
  (50, 'Rivet', 1), 
  (2, 'SubAssembly', 1), 
  (50, 'Rivet', 2), 
  (51, 'Bolt', 2), 
  (51, 'Bolt', 2), 
  (3, 'SubSubAssembly', 2), 
  (50, 'Rivet', 3), 
  (50, 'Rivet', 3), 
  (4, 'SubAssembly2', 1), 
  (50, 'Rivet', 4), 
  (51, 'Bolt', 4)

Answer 1

This recursive CTE (SQL Fiddle) should work with your sample:

WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*) 
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

Output

ParentID    Count
1           6
2           3
3           2
4           1

Note: You mentioned in comments that the question only contains a simplified sample table and real data have proper indexes and handle duplicates and data adequately.

Data used (SQL Fiddle):

DECLARE @Instances TABLE( 
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES 
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;

Answer 2

I'm not sure I understand what you mean by "amount" and where table (?) PartInstances and columns id and count come from in your sample but I calculated what I guess from your sample data.

;with ins as (
select [InstanceID], [Part],[ParentID],0 lvl
from instances where ParentID=0
union all
select i.[InstanceID], i.[Part],i.[ParentID], lvl+1
from instances i 
inner join ins on i.parentid=ins.InstanceID
)
select InstanceID,part,COUNT(*) cnt
from ins
group by instanceid,part

I hope this will give you some ideas.

Update

I understand that this is a test example but your data breaks everything starting from 1NF. Most probably your table should be break in two and normalized.