I see what you are after. The bottom line is that you could do the sub-totaling you are looking for, but it is not elegant or pretty. This is what front ends were designed to do. Get information from the user, submit a query to the backend, massage the results of the data returned by the query to be presented to the user.
Here is a query that is still only "kind of " close. It requires a self join to get the counts. I also left the RowNum column in there for comparison.
select d.doctorname, d.location, dg.name, dg.packsize,
Count(dpd2.doctorref) Total_Drugs,
ROW_NUMBER() over(partition by d.doctorname order by (select 0)) RowNum
from Doctor_Prescribed_Drugs dpd
left join Doctor d on dpd.doctorref = d.doctorref
left join drugs dg on dpd.drugref = dg.drugref
join Doctor_Prescribed_Drugs dpd2 on dpd2.doctorref = dpd.doctorref
where d.Location in ('London', 'Manchester') and dg.PackSize is not null
group by d.doctorname, d.location, dg.name, dg.PackSize
order by d.doctorname, Total_Drugs
If you really want SQL to do the running count and formatting, I would suggest you study the following article. It seems to be real close to what you are trying to accomplish:
http://www.databasejournal.com/features/mssql/article.php/3112381/SQL-Server-Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm