SQL Server does not provide any drill down queries such as Category > Sub Category. There are reporting tools for it but it cannot be done in SQL Query alone without help from theird party software.
There is SQL Group By ROLLUP, CUBE and “Group Sets”. It is a way to get rid of UNIONs and give you ‘total’ figures which gives you “some” level of drilldown (rather drill up) and can be very useful when you are interested in find total numbers by other than the group clause but it does not exactly give you drill down that a reporting tool will give. It merely calculates overall sums/totals.