view strength school wise RSS

• • Reply
  

  rabindra_lal

  Member

  515 Points

  202 Posts

  view strength school wise

  Apr 23, 2012 06:40 AM|LINK

  TableName:SchoolMaster
  SchoolID           SchoolName
  1                           Kps
  2                           DPS

  TableName:RoomMaster
  RoomID CenterID  schoolID RoomName  RoomCode  noofseat
  1                 1            1          Room1           R001        3
  2                 1            1          Room2           R002        4
  3                 1            2           Room1          R001        2
  1                 1            2           Room2          R002        5
  1                 1            2           Room3          R003        6

  
  output

  basis of centerid i want to view in a center how many school and it's strength

  SchoolName   TotalNOofSeat
  KPs                        7
  DPS                      13

  plz help me to how to write optimized query for this result....
   

• • Reply
  

  shivalthakur

  Participant

  1839 Points

  532 Posts

  Re: view strength school wise

  Apr 23, 2012 06:44 AM|LINK

  select schoolname,sum(noofseat) as TotalSeats from schoolmaster inner join roommaster on roommaster.schoolId=schoolmaster.schoolid
  
  
  
  group by schoolname

  
  
  

  if(Hard_Work && Luck)
  Response.Write("Success");
  
  
  Best Of Luck
  Shival Thakur
  • Edited by shivalthakur on Apr 23, 2012 06:44 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: view strength school wise

  Apr 23, 2012 06:48 AM|LINK

  --get total for each school
  select schoolname,ISNULL(SUM(noofseat),0) as TotalSeats
  from schoolmaster
  LEFT join roommaster on roommaster.schoolId=schoolmaster.schoolid
  group by schoolname

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  sandeepmitta...

  Contributor

  6789 Points

  1058 Posts

  Re: view strength school wise

  Apr 23, 2012 06:50 AM|LINK

  select s.SchoolName, isnull(sum(isnull(noofseat,0)),0)
  from SchoolMaster s left join RoomMaster r on s.SchoolID = r.schoolID
  group by s.SchoolName

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Apr 23, 2012 06:50 AM
  • Edited by sandeepmittal11 on Apr 23, 2012 06:51 AM
  • Edited by rabindra_lal on Apr 23, 2012 10:15 AM
  • Marked as answer by sandeepmittal11 on Apr 24, 2012 03:24 AM
• • Reply
  

  cninjas

  Contributor

  4868 Points

  851 Posts

  Re: view strength school wise

  Apr 23, 2012 06:50 AM|LINK

  hi

  you can check the below sample query for getting the sum

  select companyname ,(select sum(freight) from orders where orders.customerid = customers.customerid) as frieght
  from customers

  in your case it will 

  select schoolname ,(select sum(noofseat) from roommaster where roommaster.schoolid=SchoolMaster.schoolid) as TotalNoofSeat from SchoolMaster

  Hope it helps,thanks

  Microsoft Community Contributor 2011
  Niranjan
• • Reply
  

  vendan

  Participant

  856 Points

  293 Posts

  Re: view strength school wise

  Apr 23, 2012 09:14 AM|LINK

  Hi,

  Try below Query

  DECLARE @SchoolMaster TABLE
  (
          SchoolId INT,SchoolName VARCHAR(10)
  )
  INSERT INTO @SchoolMaster
  SELECT 1,'KPS' UNION ALL
  SELECT 2,'DPS'
  
  DECLARE @RoomMaster TABLE
  (
  RoomId INT,CenterId INT,SchoolId INT,RoomName VARCHAR(10),RoomCode VARCHAR(10),noofseat INT
  )
  
  INSERT INTO @RoomMaster
  SELECT 1,1,1,'Room1','R001',3 UNION ALL
  SELECT 2,1,1,'Room2','R002',4 UNION ALL
  SELECT 3,1,2,'Room1','R001',2 UNION ALL
  SELECT 1,1,2,'Room2','R002',5 UNION ALL
  SELECT 1,1,2,'Room3','R003',6
   
  
   
   SELECT S.SchoolName,SUM(R.noofseat) AS [TotalNOofSeat]
   FROM @RoomMaster R LEFT OUTER JOIN @SchoolMaster S ON  R.SchoolId= S.SchoolId
   GROUP BY R.CenterId,S.SchoolName

  Thanks,
  Kalaivendan
  
  Please Mark as Answer if this post helps you!
  • Marked as answer by rabindra_lal on Apr 23, 2012 10:15 AM