I am not into SQL now, but based on what I learned from school, I think it is:

Select Category, sum (Category) as Subtotal from mytable group by Category;
(don't know if keyword as should be there or not for MS SQL)

Isn't it that easy ?