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