show result based on community

Question

User364480375 posted

hi

i have below table:

SELECT [pid]
      ,[r_date]
      ,[r_month]
      ,[DistrictId]
      ,[CircleId]
      ,[cell_02]
      ,[cell_03]
      ,[cell_04]
      ,[cell_05]
      ,[cell_06]
      ,[cell_07]
      ,[cell_08]
      ,[createddate]
      ,[status]
  FROM [landrecord].[dbo].[praptra103]

my sp is like this:

ALTER procedure [dbo].[spGetpraptra103ByMonth]
(
@month int,
@year int
)
as
Begin
select 

p.DistrictId, d.DistrictHName,

sum(p.cell_03) as cell_03,


sum(p.cell_06) as cell_06,
sum(p.cell_07) as cell_07,
sum(p.cell_08) as cell_08


from praptra103 p, tblDistrict d where p.DistrictId=d.DistrictId and month(p.r_date)=@month and year(p.r_date)=@year group by  p.DistrictId, d.DistrictHName;
select p1.*,d.DistrictHName, c.CircleHName  from praptra103 p1, tblDistrict d, tblCircle c where p1.DistrictId=d.DistrictId and p1.CircleId=c.CircleId and month(p1.r_date)=@month and year(p1.r_date)=@year order by p1.CircleId
End

i am getting result like this:  i want total based on jaat community and sikh community. kindly suggest query

circle1	jaat community	3000.00	jaat community	0	0.00	0.00	0.00	0
circle2	sikh community	3000.00	sikh community	0	0.00	0.00	0.00	0
circle3	jaat community	0.00	jaat community	0	0.00	0.00	0.00	0
district	Total	6000.00				0.00

Answer 1

User364480375 posted

kindly suggest what to modify in sql query.

Answer 2

User-893317190 posted

Hi mike4u,

Because you haven't  provided all your table columns and data , it is hard to know what you want to do with your query. Could you post all your table columns and table data?

From your result and your requirement, it seems that you want to get total group by  community . You could use  union all and group by  to reorganize your  result.

I create a table according to your result. The column names are circle ,community , price, community1 and sum1.

circle1	  jaat community	3000	jaat community	 400
circle2	  sikh community	3000	sikh community	 500
circle3	  jaat community 	2000	jaat community	 600

Below is my sql to get the total price group by community.

select * from community

union all

select 'distrinct', 'total' ,sum(price),community,sum(sum1) from community
 group by community,community1 
order by community1

And the result.

circle1	   jaat community	3000	   jaat community	400
circle3	   jaat community 	2000	   jaat community	600
distrinct	total	        5000	   jaat community	1000
distrinct	total	        3000	   sikh community	500
circle2	   sikh community	3000	    sikh community	500

Best regards,

Ackerly Xu