Grouping MYSQL query results base on sum of data in multiple column

Question

I have a table with 10 columns (stdID, stdName, score1, score2..... Score8).

The table has more than 100 student record (stdID) with different scores (score1.... score8).

What I want is to make a query that will fetch all the students and group the result by sum of score1 to score8.

I.e I should be able to display the students in categories like: A. Student with total scores > 70 B. Students with total score 50 and so on.

I am using codeigniter 4.4

So far I can be able to make a query that fetch a single category:

$this->select(stdID,stdName, (score1 + score2 + ....score8) as total);
$this->groupBy(stdID);
$this->having(total > 70);
$this->findAll();

And I used foreach to loop through the results which return 5 students with total score > 70

But what I really want is multiple categories of Total score1 to score8 not only category of > 70. I don't know how to go about it please.

Answer 1

You can fetch all student records with their total scores and then use PHP to categorize them into groups based on the total score range. Here's how you can do it using CodeIgniter's Query Builder:

$query = $this->db->table('students')
                  ->select('stdID, stdName, (score1 + score2 + score3 + score4 + score5 + score6 + score7 + score8) as total')
                  ->get();

$students = $query->getResult();

// Initialize categories
$categories = [
    'A' => [],
    'B' => [],
    // other categories
];

// Categorize students based on their total score
foreach ($students as $student) {
    if ($student->total > 70) {
        $categories['A'][] = $student;
    } elseif ($student->total >= 50 && $student->total <= 70) {
        $categories['B'][] = $student;
    }
    // handle other categories
}

This code will give you an associative array $categories where each key represents a category, and the value is an array of students belonging to that category. You can add more categories as needed by adding more keys to the $categories array and adding more code to the foreach loop.