求怎样实现多字段的统计汇总?解决方法_Sql Server

求怎样实现多字段的统计汇总?
Code city district
__________________________________
305 23 10
305 23 14
305 23 14
305 23 14
305 24 9
802 2 3
802 2 4
802 2 3
803 5 6

假设一个表格如上
我想知道根据首先依据同一个city和不同的district的组合，看看不同code的出现次数
然后是一个city总的不同code的记录数
最后是不同code的汇总

比如以上输出就是:
for code 305
city district code(305)
23 10 1
23 14 3
23 * sum 4
24 9 1
14 * sum 1
* * sum 5
city district code(802)
2 3 2
2 4 1
2 * sum 3
5 6 1
5 * sum 1
* * sum 4

------解决方案--------------------

SQL code

select city,district,count(*) from tb group by city,districtselect city,count(distinct code) from tb group by cityselect distinct code from tb
------解决方案--------------------
不顯示總計
SQL codeuse Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([Code] int,[city] int,[district] int)Insert #Tselect 305,23,10 union allselect 305,23,14 union allselect 305,23,14 union allselect 305,23,14 union allselect 305,24,9 union allselect 802,2,3 union allselect 802,2,4 union allselect 802,2,3 union allselect 803,5,6GoSelect [Code],[city]=CASE WHEN GROUPING([Code])=0 AND GROUPING(district)=1 OR GROUPING([Code])=1  THEN '*' ELSE RTRIM([city]) end,[district]=CASE  WHEN GROUPING([Code])=0 AND GROUPING(district)=1 OR GROUPING([Code])=1  THEN 'sum' ELSE RTRIM(district) end,COUNT(1) AS TotalCount from #T GROUP BY [Code],[city],district WITH rollupHAVING GROUPING([Code])=0/*Code    city    district    TotalCount305    23    10    1305    23    14    3305    *    sum    4305    24    9    1305    *    sum    1305    *    sum    5802    2    3    2802    2    4    1802    *    sum    3802    *    sum    3803    5    6    1803    *    sum    1803    *    sum    1*/