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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vstackAuthor Commented:
Thank you.  Sometimes you can't see the forest for the trees.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.