CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user1274646
user1274646

Reputation: 921

To find the sum of rows based on some condition in sql server

Hi i am not able to find sum of rows in this query -

The tables are as follows:

Master_Choicecode

ChoiceCode      MainCourseId    CourseLevelId    InstituteId

Master_MainCourse

MainCourseId     MainCourseName   CourseLevelId   CourseProgram
  11                x                1             abc
  12                y                2             xyz

Master_CourseLevel

CourseLevelId    CourseLevelName     
   1                deg
   2                Dip

Master_Institute

Instituteid     InstituteName    Statusid
  1001           Insti1          100
  1002           Insti2          200

Master_InstituteStatus

StatusId   StatusName
 100            Status1
 200            Status2

Now using all these tables i want to show this:

CourseProgram   CourseLevelName  Status1(from Master_InstituteStatus)  Status2(from Master_InstituteStatus) TotalInstitutes
abc             Deg          Count(status1institute)  Count(status2institute)             Total(status1+status2)

Now this is what i have tried:

SELECT B.CourseProgram,C.CourseLevelName,
sum(case when E.InstituteStatusName =' Status1' then 1 else 0 end )as     Status1,
sum(case when E.InstituteStatusName =' Status2' then 1 else 0  end ) as  Status2,
FROM Master_ChoiceCode A 
inner join Master_MainCourse B on A.MainCourseID=B.MainCourseID
inner join Master_CourseLevel C on A.CourseLevelID=C.CourseLevelID  
inner join  Master_Institute D on A.InstituteID=D.InstituteID
inner join Master_InstituteStatus1 E on D.InstituteStatusID1=E.InstituteStatusID
where B.CourseLevelID IN(1,2)
GROUP BY B.CourseProgram,A.CourseLevelID,C.CourseLevelName,E.InstituteStatusName
order by B.CourseProgram,C.CourseLevelName;

With this i get the output like this:

CourseProgram   CourseLevelName  Status1(from Master_InstituteStatus)     Status2(from Master_InstituteStatus)    
  abc              deg                10(count of status1)                 20(count of status2)

Please tell me how should i find the sum of Status1 column and Status2 column so that i can get the last column as Total_No_ of_institutes

Upvotes: 1

Views: 1132

Answers (2)

Maarten
Maarten

Reputation: 22945

SELECT B.CourseProgram,C.CourseLevelName,
sum(case when E.InstituteStatusName =' Status1' then 1 else 0 end )as     Status1,
sum(case when E.InstituteStatusName =' Status2' then 1 else 0  end ) as  Status2,
sum(case when E.InstituteStatusName IN (' Status1', ' Status2') then 1 else 0  end ) as  Total_No_of_institutes,

FROM Master_ChoiceCode A 
inner join Master_MainCourse B on A.MainCourseID=B.MainCourseID
inner join Master_CourseLevel C on A.CourseLevelID=C.CourseLevelID  
inner join  Master_Institute D on A.InstituteID=D.InstituteID
inner join Master_InstituteStatus1 E on D.InstituteStatusID1=E.InstituteStatusID
where B.CourseLevelID IN(1,2)
GROUP BY B.CourseProgram,A.CourseLevelID,C.CourseLevelName,E.InstituteStatusName
order by B.CourseProgram,C.CourseLevelName;

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13486

SELECT B.CourseProgram,C.CourseLevelName,
sum(case when E.StatusName=' Status1' then 1 else 0 end )as Status1,
sum(case when E.StatusName=' Status2' then 1 else 0  end ) as  Status2,
FROM Master_ChoiceCode A 
inner join Master_MainCourse B on A.MainCourseID=B.MainCourseID
inner join Master_CourseLevel C on A.CourseLevelID=C.CourseLevelID  
inner join  Master_Institute D on A.InstituteID=D.InstituteID
inner join Master_InstituteStatus1 E on D.InstituteStatusID1=E.InstituteStatusID
where B.CourseLevelID IN(1,2)
GROUP BY B.CourseProgram,A.CourseLevelID,C.CourseLevelName,E.InstituteStatusName
order by B.CourseProgram,C.CourseLevelName;

Upvotes: 0

Related Questions