*************

SQL code: Upload Copy Format Clear

CREATE TABLE Participant ( id int, name varchar(100), gender varchar(100), age int, user_id int ); INSERT INTO Participant (id, name, gender, age, user_id) VALUES (1, 'nameA', 'male', 8, 1), (2, 'nameA', 'female', 10, 1), (3, 'nameA', 'male', 8, 1), (4, 'nameA', 'female', 8, 1); SELECT * FROM Participant;

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php $user_id = 1; var_export( $db::table('Participant') ->select('age') ->selectRaw("SUM(gender = 'male') male") ->selectRaw("SUM(gender = 'female') female") ->selectRaw('COUNT(1) count_gender') ->where('user_id', $user_id) ->groupBy('age', 'gender') ->orderBy('age', 'ASC') ->get() ->groupBy('age') ->map(fn($group, $gender) => [ 'age' => $group->first('age'), 'countAll' => $group->sum('count_gender'), 'gender' => [ 'genderName' => $gender, 'countGender' => array_sum($group->pluck('count_gender') ] ] ) ->toArray() ); /* foreach ($participants as $participant) { $genderData = [ [ 'genderName' => 'male', 'countGender' => $participant->countMale ?? 0, ], [ 'genderName' => 'female', 'countGender' => $participant->countFemale ?? 0, ] ]; $result[] = [ 'age' => $participant->age, 'countAll' => $participant->countAll, 'gender' => $genderData, ]; } $jsonOutput = json_encode($result);*/

PHP version:

Show: Warnings Notice

Result: Copy Clear

PHPize Online / SQLize Online / SQLtest Online