How to calculate average or sum using data from a Ballerina table with query expressions?

Question

I have a Ballerina table (table<Student> key(subject,studentId)) with fields such as studentId, subject, marks. Marks are given for each subject.

I need to,

1. Find the average/sum of each student and construct another table
2. Rank the students(sort by the student scores).

How can I do them with query expressions in Ballerina.

Answer 1

You can use the group by clause in query expressions to calculate the average and sum and the order by clause to rank the students. Below is an example demonstration.

Take subjectScores as the table containing subject and score details.

type SubjectScore record {|
    readonly string subject;
    readonly string studentId;
    int score;
|};

table<SubjectScore> key(subject, studentId) subjectsScores = table [
        {subject: "A", studentId: "1", score: 100},
        {subject: "A", studentId: "2", score: 90},
        {subject: "A", studentId: "3", score: 80},
        {subject: "B", studentId: "1", score: 80},
        {subject: "B", studentId: "2", score: 70},
        {subject: "B", studentId: "3", score: 60},
        {subject: "C", studentId: "1", score: 60},
        {subject: "C", studentId: "2", score: 50},
        {subject: "C", studentId: "3", score: 40}
    ];

We can have a new table defined to enter the total scores and average scores of each student by grouping the above table entries by studentId and then rank the students according to the scores using order by.

type ProcessedScore record {|
    readonly string studentId;
    int totalScore;
    decimal averageScore;
|};

public function main() returns error? {
    ProcessedScore[] processedScores =
        from var {studentId, score} in subjectsScores
            group by studentId
            select {studentId, totalScore: sum(score), averageScore: avg(score)};

    ProcessedScore[] rankedStudents = from ProcessedScore score in processedScores
        order by score.totalScore descending
        select score;
}

rankedStudents would contain the following results.

[{"studentId":"1","totalScore":240,"averageScore":80},
{"studentId":"2","totalScore":210,"averageScore":70},
{"studentId":"3","totalScore":180,"averageScore":60}]