SQL Dynamic SUM Column Values

Question

I have this sql, but what I wanted to do was to have another column that had the total number of columns of other matches like this ...

declare @SchoolID int declare @RoleID int declare @match int declare @firstname varchar set @SchoolID=1 set @RoleID=1 SELECT dbo.Teacher.Firstname, dbo.School.SchoolName, dbo.School.SchoolID, dbo.RoleTitle.RoleTitle, CASE WHEN dbo.School.SchoolID = @SchoolID then 1 else 0 end as [SchoolMatch], CASE WHEN dbo.JobTitle.RoleTitleID = @RoleID then 1 else 0 end as [RoleMatch] FROM dbo.School INNER JOIN dbo.Lesson ON dbo.School.SchoolID = dbo.Lesson.SchoolID INNER JOIN dbo.RoleTitle ON dbo.Lesson.RoleTitleID = dbo.RoleTitle.RoleTitleID INNER JOIN dbo.Teacher ON dbo.Lesson.TeacherID = dbo.Teacher.TeacherID 

Answer 1

You can simply repeat your case statements and add them.

 SELECT . . CASE WHEN dbo.School.SchoolID = @SchoolID then 1 else 0 end + CASE WHEN dbo.JobTitle.RoleTitleID = @RoleID then 1 else 0 end as [NewColumn] 

Answer 2

You can do something like this:

 SELECT dbo.Teacher.Firstname, dbo.School.SchoolName, dbo.School.SchoolID, dbo.RoleTitle.RoleTitle, CASE WHEN dbo.School.SchoolID = @SchoolID then 1 else 0 end as [SchoolMatch], CASE WHEN dbo.JobTitle.RoleTitleID = @RoleID then 1 else 0 end as [RoleMatch], (CASE WHEN dbo.School.SchoolID = @SchoolID then 1 else 0 end)+ (CASE WHEN dbo.JobTitle.RoleTitleID = @RoleID then 1 else 0 end) AS NewColumn FROM dbo.School INNER JOIN dbo.Lesson ON dbo.School.SchoolID = dbo.Lesson.SchoolID INNER JOIN dbo.RoleTitle ON dbo.Lesson.RoleTitleID = dbo.RoleTitle.RoleTitleID INNER JOIN dbo.Teacher ON dbo.Lesson.TeacherID = dbo.Teacher.TeacherID