PostgreSQL Querying and Joining Table

Question

I'm still new in doing some querying work, so please have mercy. I have three tables called Student, QuizResult, and Level.

Here is the tables and their relations

                 Student                               Level
| student_id |    name    | level_id |       | level_id | level_name |
|:----------:|:----------:|:--------:|       |:--------:|:----------:|
|      1     |    John    |     1    |   >   |     1    |   Rookie   |
|      2     |    Jane    |     2    |       |     2    |   Expert   |

                    v

               QuizResult
|   quiz_id  | student_id |   score  |
|:----------:|:----------:|:--------:|
|      1     |      1     |    40    |
|      1     |      1     |    100   |
|      2     |      1     |    80    |
|      1     |      2     |    100   |
|      2     |      2     |    100   |

So what I am gonna do is selecting the maximum score if the quiz_id has more than one entry, and then summing the quiz score grouped by student id. What I'm looking for is kinda like this

| student_id |    name    |  level   |   total_score  |
|:----------:|:----------:|:--------:|:--------------:|
|      1     |    John    |  Rookie  |       180      |
|      2     |    Jane    |  Expert  |       200      |

What I've tried is something like this

SELECT quiz_id, student_id, MAX(score)
FROM QuizResult
GROUP BY student_id, quiz_id

The rest, well..., I'm kinda lost since I've tried using "SELECT in" from above code.

Any kind of help will be appreciated. Thank you.

@GordonLinoff thank you very much, though I'm using PostgreSQL and do some raw query for this question so I'll add that one. — Randi Pratama
– Randi Pratama, Commented Jul 3, 2020 at 2:35

sticky bit · Accepted Answer · 2020-07-03 02:47:25Z

You can use two levels of aggregation. First get the maximum score per quit an student and them sum these figures. Finally join for the additional information like the student and level names.

SELECT x2.student_id,
       s.name,
       l.level_name,
       x2.total_score
       FROM (SELECT x1.student_id,
                    sum(x1.max_score) total_score
                    FROM (SELECT qr.student_id,
                                 max(qr.score) max_score
                                 FROM quizresult qr
                                 GROUP BY qr.quiz_id,
                                          qr.student_id) x1
                    GROUP BY x1.student_id) x2
            INNER JOIN student s
                       ON s.student_id = x2.student_id
            INNER JOIN level l
                       ON l.level_id = s.level_id;

Collectives™ on Stack Overflow

PostgreSQL Querying and Joining Table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related