Hi i am trying get sub total for column and rows but my pivot query returns duplicate value. If write pivot with id column it gives perfect values but if write query with another column i am getting duplicate values.
Here is my query !
 
select

case when convert(varchar,dist) is null then 'Grand Total'
else dist end district,
case
when dis is null then ''
else dis end Edist_name,
sum([P]) as pend,sum([A]) as App,sum([R]) as Rej,sum([P]+[A]+[R]) as Gr


from
(select distinct b.Edist_name as dis, convert(varchar,a.district) as dist,a.recpno,status, count(*) over(partition by status) as total
from loanee_greivance_details a,grievances d,Merged_DebtRelief_AfterVJC.dbo.Dist_master b
where a.bankcode=d.bankcode and a.branchcode=d.branchcode and a.LoanAcno=d.LoanAcno
and a.GroupId=d.GroupId and a.GIO=d.GIO and a.district=b.dist_code and a.RecpNo=d.RecpNo
and a.district=d.district) p
pivot (count(recpno) for status in ([P] ,[R],[A] )) as pvt group by pvt.dist,pvt.dis with rollup
 
 
The result of above query is
 
id name        pend App Rej Gr
1 Srikakulam 11864 0 0 11864
1                 11864 0     0 11864
10 Chittoor          54689 56 0 54745
10                 54689 56 0 54745
11 Y.S.R.
Cuddapah         19878 0 0 19878
11                 19878 0 0 19878
12 Ananth
apuramu        30445 1 0 30446
12                30445 1 0 30446
13 Kurnool        25706 0 0 25706
13                25706 0 0 25706
2Vizianagaram 4669 0 0 4669
2                 4669 0 0 4669
3Visakhapatnam 15960 0 0 15960
3                 15960 0 0 15960
4East Godavari 24578 2 0 24580
4                         24578 2 0 24580
5West Godavari 30704 0 0 30704
5                         30704 0 0 30704
6Krishna                  35740 1378 2 37120
6                        35740 1378 2 37120
7Guntur                32697 0 0 32697
7               32697 0 0 32697
8Prakasam       39929 1 0 39930
8               39929 1 0 39930
9Sri Potti
Sriramulu
Nellore              22753 371 73 23197
9             22753         371 73 23197
Grand Total 349612 1809 75 351496