locked
show result based on community RRS feed

  • 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
    Thursday, September 13, 2018 2:03 PM

All replies

  • User364480375 posted

    kindly suggest what to modify in sql query.

    Thursday, September 13, 2018 3:15 PM
  • 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

    Friday, September 14, 2018 4:30 AM