Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Create a Model for Student

    class Student extends Model
{
    protected $table = 'students';

    public function getStudentsByScoreCategory()
    {
        $query = $this->db->table($this->table)
            ->select('stdID, stdName, (score1 + score2 + score3 + score4 + score5 + score6 + score7 + score8) as total_score')
            ->groupBy('stdID')
            ->orderBy('total_score', 'DESC');

        return $query->get()->getResult();
    }
}

    In your controller access the method

    class YourController extends BaseController
{
    public function index()
    {
        $model = new Student();
        $data['students'] = $model->getStudentsByScoreCategory();

        return view('your_view', $data);
    }
}

    And then display it on view

    <?php foreach ($students as $student): ?>
    <?php
    $totalScore = $student->total_score;

    if ($totalScore > 70) {
        $category = 'A';
    } elseif ($totalScore > 50) {
        $category = 'B';
    } else {
        $category = 'C';
    }
    ?>

    <p>Student ID: <?= $student->stdID ?>, Name: <?= $student->stdName ?>, Total Score: <?= $totalScore ?>, Category: <?= $category ?></p>
<?php endforeach; ?>

    If the score column is dynamic(N number of columns) then you can change this getStudentsByScoreCategory() method to

    public function getStudentsByScoreCategory()
    {
        // Adjust N to the maximum score column index
        $N = 8;

        $scoreColumns = implode(' + ', array_map(function ($i) {
            return "score$i";
        }, range(1, $N)));

        $query = $this->db->table($this->table)
            ->select("stdID, stdName, ($scoreColumns) as total_score")
            ->groupBy('stdID')
            ->orderBy('total_score', 'DESC');

        return $query->get()->getResult();
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search