Pivot returns duplicate values

Question

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