Home > Software engineering >  Consolidate Rows with SQL
Consolidate Rows with SQL

Time:07-29

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.

CodePudding user response:

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.

CodePudding user response:

select student_name, test_name
    , count(question_attempt), sum(answered_correctly) 
from table
group by student_name, test_name
  • Related