title: Operand data type varchar is invalid for sum operator - Procedure Error Correction
body: <p>I am using SQL Server - 2005...</p>
  
  <pre><code>ALTER Proc [dbo].[ExamResult] (@Course_Id varchar(100), @Semester varchar(10))
  as
  begin
      declare @subjname varchar(100)  
      declare @subjects varchar(7000)  
      declare @subjectsselection varchar(7000)  
      declare @SumSelection varchar(7000)  
      declare @NoOfSubjects int
      set @NoOfSubjects = 0
  
      set @subjects = ''  
      set @subjectsselection = '' 
      set @SumSelection = ''
  
      DECLARE subject_cursor CURSOR  
      FOR SELECT distinct Subject_Name FROM Exam_Result where course_id = @Course_Id And Semester = @Semester 
  
      OPEN subject_cursor  
  
      FETCH NEXT FROM subject_cursor  
      INTO @subjname  
  
      WHILE @@FETCH_STATUS = 0  
      BEGIN  
          set @subjects = @subjects + '[' + @subjname + '],'  
          set @subjectsselection = @subjectsselection + 'Sum(Isnull([' + @subjname + '],0)) As [' + @subjname + '],' 
          set @SumSelection = @SumSelection + 'Sum(Isnull([' + @subjname + '],0))+' 
  
          set @NoOfSubjects = @NoOfSubjects + 1
  
          FETCH NEXT FROM subject_cursor  
          INTO @subjname  
      End  
      CLOSE subject_cursor;  
      DEALLOCATE subject_cursor;  
  
      select @subjects = LEFT(@subjects, LEN(@subjects) - 1)  
      select @subjectsselection = LEFT(@subjectsselection, LEN(@subjectsselection) - 1)  
      select @SumSelection = LEFT(@SumSelection, LEN(@SumSelection) - 1)  
  
      print @subjects  
      print @subjectsselection  
      print @SumSelection
  
      declare @query nvarchar(4000)  
  
      set @query = 'select S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, ' + @subjectsselection + ',' 
      set @query = @query + 'Exam_Type,' + @SumSelection + ' As Grand_Total, '
      set @query = @query + '(' + @SumSelection + ')' + '/' + convert(varchar(10),@NoOfSubjects) + ' As Avg'
      set @query = @query + ' From '  
      set @query = @query + '(select Enroll_Number, Student_Name, Course_Id, Semester, Subject_Name, MarksObtained, Exam_Type from Exam_Result ) ps '  
      set @query = @query + ' pivot(sum(MarksObtained) for Subject_Name in (' + @subjects + ')) as pvt'  
      set @query = @query + ' inner join Stud_Info S on S.Enroll_Number = pvt.Enroll_Number '
      set @query = @query + ' where pvt.Course_Id = ''' + @Course_Id + ''' and pvt.Semester = ''' + @Semester + ''''
      set @query = @query + ' group by S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, Exam_Type'
      print @query
      exec sp_executesql @query  
  end
  </code></pre>
  
  <p><strong>Exam_Result table</strong>:</p>
  
  <pre><code>CREATE TABLE [dbo].[Exam_Result](
      [Result_Id] [int] IDENTITY(1,1) NOT NULL,
      [Enroll_Number] [varchar](50) NULL,
      [Student_Name] [varchar](100) NULL,
      [Course_Id] [varchar](50) NULL,
      [Semester] [varchar](50) NULL,
      [Subject_Id] [varchar](50) NULL,
      [Subject_Name] [varchar](50) NULL,
      [MarksObtained] [Varchar](10) NULL,
      [Exam_Type] [varchar](50) NULL)
  </code></pre>
  
  <p><strong>Sample data query</strong>...</p>
  
  <pre><code>INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','1','PS1','ps1','60','Final')
  INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','70','Final')
  INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','80','Final')
  INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','20','Internal')
  INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','22','Internal')
  </code></pre>
  
  <p><strong>Student_Info table</strong>: I just use for retrieved <strong>Student_Information</strong> like Enroll_Number...</p>
  
  <pre><code>CREATE TABLE [dbo].[Stud_Info](
      [Enroll_Number] [varchar](20) NOT NULL,
      [Student_Name] [varchar](20) NULL,
      [Course_Type] [varchar](20) NULL,
      [Course_Id] [varchar](20) NULL,
      [Semester] [varchar](20) NULL,
      [Password] [varchar](20) NULL,
      [Gender] [varchar](6) NULL,
      [City] [varchar](15) NULL,
      [Mobile_Number] [varchar](10) NULL)
  </code></pre>
  
  <p>It gives me error like this...</p>
  
  <blockquote>
    <p>Msg 8117, Level 16, State 1, Line 1<br>
    Operand data type varchar is invalid for sum operator.</p>
  </blockquote>
  
  <p><strong>Rahul</strong>: as per you answer I updated like this but it still gives error......</p>
  
  <pre><code>set @query = @query + ' pivot(sum(cast(MarksObtained as int)) for Subject_Name in (' + @subjects + ')) as pvt'  
  </code></pre>
  
  <p>I think it gives error because I am using the <code>MarksObtained</code> column (<code>as varchar(10)</code>)...plz give me a suggestion how I can convert <code>varchar</code> to <code>int</code> so that summation of total <code>MarksObtained</code> can be done.</p>