CODEWITHSUNDEEP
sqlpostgresql
Randi Pratama
Randi Pratama

Reputation: 112

PostgreSQL Querying and Joining Table

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.

Upvotes: 0

Views: 35

Answers (1)

sticky bit
sticky bit

Reputation: 37497

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;

Upvotes: 1

Related Questions