How to aggregate a result set using SUM?

Hello experts,

I have a sql query that aggregates stats based on instructor comments.  The comments are hard coded.  The instructor simply checks which comments apply.
I seem to be able to get the correct numbers but I want the result set to be one row and not a row for every comment.

I have posted the proc and the result set.

ALTER PROCEDURE [dbo].[qGetInstructorCommentStatsByPkEmployeeID]
@pkEmployeeID int,
@pkSectionID int,
@CommentStartDate datetime,
@CommentEndDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.pkEmployeeID, tblEmployees.StudentID,
      Sum(CASE WHEN tblInstructorComments.InstructorComment = 'Calculator use inconsistent' THEN 1 ELSE 0 END ) as NumCalculatorInconsistent,
      Sum(CASE WHEN tblInstructorComments.InstructorComment = 'Changes not signed' THEN 1 ELSE 0 END ) as NumChangesNotSigned,
      Sum(CASE WHEN tblInstructorComments.InstructorComment = 'SV stamp used on deposits' THEN 1 ELSE 0 END ) as NumSVStampOnDeposits
      From tblEmployees
      INNER Join tblInstructorComments
      On tblEmployees.pkEmployeeID = tblInstructorComments.pkEmployeeIDStudent
      Where (tblEmployees.pkEmployeeID = @pkEmployeeID)
      AND (tblInstructorComments.CommentDate Between @CommentStartDate and @CommentEndDate)
      AND (tblInstructorComments.pkSectionID = @pkSectionID)
      Group By tblInstructorComments.InstructorComment, tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.pkEmployeeID, tblEmployees.StudentID
      --Order By NumCalculatorInconsistent Desc, NumChangesNotSigned Desc, NumSVStampOnDeposits Desc
END

Result Set (I deleted the column headings cause the text wrapped):

Oates      Paxton      22      60030021      0      0      0
Oates      Paxton      22      60030021      1      0      0
Oates      Paxton      22      60030021      0      1      0
Oates      Paxton      22      60030021      0      0      1

I want the result set to be one row like:

Oates      Paxton      22      60030021      1      1      1



Thank you
vstackAsked:
Who is Participating?
 
ThomasianCommented:
Just remove "tblInstructorComments.InstructorComment" from the GROUP BY clause.

i.e.

Change

Group By tblInstructorComments.InstructorComment, tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.pkEmployeeID, tblEmployees.StudentID

TO

Group By tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.pkEmployeeID, tblEmployees.StudentID
0
 
vstackAuthor Commented:
Thank you.  Sometimes you can't see the forest for the trees.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.