samedi 17 avril 2021

I want to get the students count with age break down using Laravel 7

I wrote the query to get the student's age but I want to have the breakdowns with age and the count. This is my query to getting each student's age.

$students = DB::table('students')->select(DB::raw('TIMESTAMPDIFF(YEAR, stud_dob, CURDATE())+0 AS age'))->orderBy('age')->get();

And I used one query to get the breakdowns but it's giving the wrong count for the students.

$ranges = [ // the start of each age-range.
            '18-24' => 18,
            '25-35' => 25,
            '36-45' => 36,
            '46+' => 46
        ];

$output = Student::where('stud_flag','1')
            ->get()
            ->map(function ($user) use ($ranges) {
                $age = Carbon::parse($user->stud_dob)->age;
                foreach($ranges as $key => $breakpoint)
                {
                    if ($breakpoint >= $age)
                    {
                        $user->range = $key;
                        break;
                    }
                }
                return $user;
            })
            ->mapToGroups(function ($user) {
                return [$user->range => $user];
            })
            ->map(function ($group) {
                return count($group);
            })
            ->sortKeys();

I want the output like. Please help me to fix this issue.

Range      Count 
------    --------

18 - 24     20
25 - 35     15
36 - 45     18
46+         30


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire