Consolidate Rows with SQL

Question

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.

score 4 · Accepted Answer · edited Jul 28 '22 at 22:23

4

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.

edited Jul 28 '22 at 22:23

Dale K

25,246
15
42
71

answered Jul 28 '22 at 19:53

EdmCoff

3,506
1
9
9

score 2 · Answer 2 · edited Jul 28 '22 at 20:58

2

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

edited Jul 28 '22 at 20:58

Dale K

25,246
15
42
71

answered Jul 28 '22 at 19:50

Stig

1,974
2
23
50

Consolidate Rows with SQL

2 Answers2