Somme dynamique SQL des valeurs d'une colonne

Question

J'ai ce sql mais ce que je cherchais à faire était d'avoir une autre colonne qui avait le total de l'autre colonne de correspondance comme ceci...

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

Vous pouvez simplement répéter vos déclarations de cas et les ajouter.

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

Vous pouvez faire quelque chose comme ça :

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