Calculate Total NO of seat RSS

• • Reply
  

  rabindra_lal

  Member

  515 Points

  202 Posts

  Calculate Total NO of seat

  Apr 19, 2012 07:14 AM|LINK

  CenterID DistricID CenterName
  1    2               Dav
  2    2    Kps
  3    2    Mansover
  4    1    Dav
  5    1    KPS
  6    1    Stervis
  
  
  TableName : Orps_RoomTYpe
  
  RoomID CenterID DistrictID NOOFSeat
  1 3   2 10
  2              3   2 20
  3              4                 1 5
  4              4                 1 12
  5 6   1 21
  6 5   1 48
  
  
  
  Ouput:Basis of Districtid Example DistrictID=1
  
  CenerName  NOofSeat
  Dav      17
  Stervis      21
  KPS      48
  	
  Ouput:Basis of Districtid Example DistrictID=2
  
  CenerName   NOofSeat
  Mansover                      30

  How to write the query for this resultset. plz help me

• • Reply
  

  mm10

  Contributor

  6395 Points

  1182 Posts

  Re: Calculate Total NO of seat

  Apr 19, 2012 11:15 AM|LINK

  Here you go:

  SELECT
  c.CenterName, SUM(o.NOofSeat)
  FROM Orps_RoomTYpe o
  JOIN District c ON o.CenterID = c.CenterID AND o.DistrictID = c.DistricID
  WHERE o.DistrictID=1
  GROUP BY c.CenterName

  Please 'Mark as Answer' if this post helped you.
  • Marked as answer by Chen Yu - MSFT on Apr 23, 2012 09:42 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Calculate Total NO of seat

  Apr 19, 2012 11:21 AM|LINK

  declare @table table
  (
  	CenterID int,DistricID int,CenterName varchar(100)
  )
  insert into @table
  select 1,2,'Dav' union all
  select 2,2,'Kps' union all
  select 3,2,'Mansover' union all
  select 4,1,'Dav' union all
  select 5,1,'KPS' union all
  select 6,1,'Stervis'
  
  declare @Orps_RoomTYpe table
  (
  	RoomID int,CenterID int,DistrictID int,NOOFSeat int
  )
  insert into @Orps_RoomTYpe
  select 1,3,2,10 union all
  select 2,3,2,20 union all
  select 3,4,1,5  union all
  select 4,4,1,12  union all
  select 5,6,1,21  union all
  select 6,5,1,48
  
  select t1.CenterName,SUM(NOOFSeat) AS TotalSeat
  from @table as t1
  INNER JOIN @Orps_RoomTYpe as t2 On t1.CenterID=t2.CenterID
  --where condition added here
  GROUP BY t1.CenterName
  
  
  
  --output
  -------------------
  --CenterName TotalSeat
  --Dav 17
  --KPS 48
  --Mansover 30
  --Stervis 21

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by Chen Yu - MSFT on Apr 23, 2012 09:42 AM