Leaderboard

Using an "age_group" table as suggested and a test table with 999 random records ... +--------------+------------+--------+--------+ +--------+---------------+------+-----+---------+----------------+ | age_group_id | group_name | lo_age | hi_age | | Field | Type | Null | Key | Default | Extra | +--------------+------------+--------+--------+ +--------+---------------+------+-----+---------+----------------+ | 1 | Under 18 | 0 | 17 | | id | int(11) | NO | PRI | NULL | auto_increment | | 2 | 18 - 29 | 18 | 29 | | name | varchar(30) | YES | | NULL | | | 3 | 30 - 39 | 30 | 39 | | age | tinyint(4) | YES | | NULL | | | 4 | 40 - 49 | 40 | 49 | | gender | enum('M','F') | YES | | NULL | | | 5 | 50 - 59 | 50 | 59 | +--------+---------------+------+-----+---------+----------------+ | 6 | 60 - 69 | 60 | 69 | | 7 | 70 - 79 | 70 | 79 | | 8 | 80+ | 80 | 120 | +--------------+------------+--------+--------+ ... then ... SELECT group_name as `Age Group` , SUM(gender='M') as Male , SUM(gender='F') as Female , COUNT(*) as Total FROM tb_infected i JOIN age_group a ON i.age BETWEEN a.lo_age AND a.hi_age GROUP BY age_group_id; giving +------------+------+--------+-------+ | Age Group | Male | Female | Total | +------------+------+--------+-------+ | Under 18 | 70 | 98 | 168 | | 18 - 29 | 44 | 72 | 116 | | 30 - 39 | 42 | 50 | 92 | | 40 - 49 | 54 | 56 | 110 | | 50 - 59 | 47 | 62 | 109 | | 60 - 69 | 42 | 68 | 110 | | 70 - 79 | 36 | 68 | 104 | | 80+ | 76 | 114 | 190 | +------------+------+--------+-------+

Sort the array first. Assuming you start with ... $options = [ [ 'type' => 'Visual disability', 'name' => 'Audio-described cut-scenes' ], [ 'type' => 'Visual disability', 'name' => 'Highlighted path to follow' ], [ 'type' => 'Physical disability', 'name' => 'Sensitivity settings for all the controls' ], [ 'type' => 'Visual disability', 'name' => 'Screen readers on menus' ], [ 'type' => 'Visual disability', 'name' => 'Slow down the game speed' ], ]; then ... # # Sort the array by type (descending) # usort($options, function($a, $b) { return $b['type'] <=> $a['type']; }); # # process the array # $prev_type = ''; // store previous type echo "<ul>\n"; foreach ($options as $opt) { if ($opt['type'] != $prev_type) { // is this a new type? if ($prev_type != '') { // was there a previous one? echo "</ul>\n</li>\n"; // if so, close it } echo "<li>{$opt['type']}\n<ul>\n"; $prev_type = $opt['type']; // store as previous value } echo "<li>{$opt['name']}</li>\n"; } // close last group echo "</ul>\n</li>\n"; // close whole list echo "</ul>\n"; giving ... <ul> <li>Visual disability <ul> <li>Audio-described cut-scenes</li> <li>Highlighted path to follow</li> <li>Screen readers on menus</li> <li>Slow down the game speed</li> </ul> </li> <li>Physical disability <ul> <li>Sensitivity settings for all the controls</li> </ul> </li> </ul> An alternative approach is to reorganise the array using subarrays for each type... $options = [ 'Visual disability' => [ 'Audio-described cut-scenes', 'Highlighted path to follow', 'Screen readers on menus', 'Slow down the game speed' ], 'Physical disability' => [ 'Sensitivity settings for all the controls' ] ]; then use two nested foreach() loops.

1 ) You want to rank the total score so you need to get that in the lowest level subquery 2) you can't apply the grade to the total score (ranges don't apply). I have applied it to the students' average scores. Hopefully, this is what you want... +--------+------------+-------+-------+------+----------------+---------------+-----------+ | yearid | semesterid | regno | total | rank | Total students | Average grade | comment | +--------+------------+-------+-------+------+----------------+---------------+-----------+ | 1 | 1 | 4663 | 865 | 2 | 2 | B2 | V Good | | 1 | 1 | 6073 | 969 | 1 | 2 | A | Excellent | +--------+------------+-------+-------+------+----------------+---------------+-----------+ Query SELECT yearid , semesterid , regno , total , rank , numstudents as `Total students` , grade as `Average grade` , comment FROM ( SELECT yearid , semesterid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno , armsLevelId , armsid , avgtotal FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, armsid, armsLevelId) as yss , regno , SUM(total) as total , ROUND(AVG(total)) as avgtotal , armsLevelId , armsid FROM subject_position GROUP BY yearid, semesterid, armsid, armsLevelId,regno ORDER BY yearid, semesterid, armsid, armsLevelId, total DESC ) sorted JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init ) ranked JOIN grade ON avgtotal BETWEEN grade.lomark and grade.himark JOIN ( SELECT yearid , semesterid , armsLevelId , armsid , COUNT(DISTINCT regno) as numstudents FROM subject_position GROUP BY yearid, semesterid, armsid, armsLevelId ) students USING (yearid, semesterid, armsid, armsLevelId) WHERE -- regno = 4663 and -- uncomment for individual student armsLevelId='1' and armsId='1' and semesterid='1' and yearid='1' ORDER BY regno;