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.