Skip to content
Advertisement

Consolidate Rows with SQL

Trying to consolidate test results so that each test is in one row. Database I am pulling from has an answer result per row. Sample Data:

Student Name Test Name Question Attempt Answered Correctly (0 = no, 1= yes)
Mary Intro Assessment 1 1
Mary Intro Assessment 1 0
Mary Intro Assessment 1 1
Joseph Intro Assessment 1 1
Joseph Intro Assessment 1 1
Joseph Intro Assessment 1 1
Tom Intro Assessment 1 0
Tom Intro Assessment 1 0
Tom Intro Assessment 1 1

Desired result if possible:

Student Name Test Name Question Attempt Answered Correctly
Mary Intro Assessment 3 2
Joseph Intro Assessment 3 3
Tom Intro Assessment 3 1

Any guidance would be appreciated.

Advertisement

Answer

It sounds like you just want a basic GROUP BY with some SUMs:

SELECT "Student Name", "Test Name"
    , SUM("Question Attempt") "Question Attempt"
    , SUM("Answered Correctly") "Answered Correctly"
FROM mytable
GROUP BY "Student Name", "Test Name"

GROUP BY "Student Name", "Test Name" means you want one row per unique student/test and SUM explains how you want the other data combined.

1 People found this is helpful
Advertisement