Try this out the_hareeb.

select ID,name,Category, SUM(Children)as Total_Children

from myBaseTable

group by ID,name,Category with rollup

here is another one that gives you exactly what you wanted:

Select Id, Name, Category=Case when ig=1 then 'SUBTOTAL' else Category end, Children

Select

Id, Name, Category, Children=Sum(Children),

ig=Grouping(Id), ng=Grouping(Name), cg=Grouping(Category)

From myBaseTable

Group by rollup (category,Id,Name)

) Grouped

Where ng = ig and cg <> 1

Thanks!