# view strength school wise RSS

## 5 replies

Last post Apr 23, 2012 09:14 AM by vendan

Member

515 Points

202 Posts

### view strength school wise

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....

Participant

1839 Points

532 Posts

### Re: view strength school wise

```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

Star

14460 Points

2402 Posts

### Re: view strength school wise

```--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```

Contributor

6789 Points

1058 Posts

### Re: view strength school wise

```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

Contributor

4868 Points

851 Posts

### Re: view strength school wise

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

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

Participant

856 Points

293 Posts

### Re: view strength school wise

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