php – Get max score according to subject_id

I have a problem where I’m trying to get the highest score in a subject in a class.

Something like this:

student_id | subject_id  | get_ca1  | get_ca2  | get_exam | total
--------------------------------------------------------------------
   101     |     1       | 10       | 7        | 10       |  27
   102     |     2       | 5        | 5        | 10       |  20
   103     |     1       | 9        | 10       | 4        |  23
   101     |     1       | 8        | 10       | 10       |  28
   103     |     2       | 2        | 10       | 10       |  22
   104     |     1       | 7        | 8        | 5        |  20
   101     |     2       | 7        | 8        | 5        |  20

Where the highest score in subject_id = 1 is 28

What I’m doing only gives me the highest score of all the subjects of a particular student. For example, it gives me highest score for student_id = 101 as 28 and student_id = 103 as 23

This is the query that gets me the scores of a particular student. Mind you, this is where I tried to get my max score for a particular subject, so I know why it’s not giving me what I want.

 public function getStudentResultByExam($exam_id, $student_id) {
        $sql = "SELECT exam_group_class_batch_exam_subjects.*,exam_group_exam_results.id as `exam_group_exam_results_id`,exam_group_exam_results.attendence,exam_group_exam_results.get_marks,exam_group_exam_results.get_ca1,exam_group_exam_results.get_ca2,exam_group_exam_results.get_ca3,exam_group_exam_results.get_ca4,exam_group_exam_results.get_ca5,exam_group_exam_results.get_ca6,exam_group_exam_results.get_exam,exam_group_exam_results.get_tot_score,MAX(exam_group_exam_results.get_tot_score) AS total_max,exam_group_exam_results.note,exam_group_exam_results.expected,exam_group_exam_results.achieved,exam_group_exam_results.remark,subjects.name,subjects.code FROM `exam_group_class_batch_exam_subjects` inner JOIN exam_group_exam_results on exam_group_exam_results.exam_group_class_batch_exam_subject_id=exam_group_class_batch_exam_subjects.id INNER JOIN exam_group_class_batch_exam_students on exam_group_exam_results.exam_group_class_batch_exam_student_id=exam_group_class_batch_exam_students.id and exam_group_class_batch_exam_students.id=" . $this->db->escape($student_id) . " INNER JOIN subjects on subjects.id=exam_group_class_batch_exam_subjects.subject_id WHERE exam_group_class_batch_exam_subjects.exam_group_class_batch_exams_id=" . $this->db->escape($exam_id);
        
        $query = $this->db->query($sql);
        return $query->result();
    }

However, the query below gives me exactly what I want but for some reason, I want it to be unionized with the above query.

SELECT `exam_group_class_batch_exam_subject_id`,
MAX(`get_tot_score`) AS total_max
FROM exam_group_exam_results
GROUP BY `exam_group_class_batch_exam_subject_id`

So what I’m really asking is if there is a way to join both queries together in such a way that it gives me highest score for subjects and not per student?

This is how I echo it out:

<td><?php echo $exam_result_value->total_max; ?></td>                

EDIT

This is my exam_group_exam_results table:

CREATE TABLE `exam_group_exam_results` (
  `id` int(11) NOT NULL,
  `exam_group_class_batch_exam_student_id` int(11) NOT NULL,
  `exam_group_class_batch_exam_subject_id` int(11) DEFAULT NULL,
  `attendence` varchar(10) DEFAULT NULL,
  `get_marks` int(11) DEFAULT NULL,
  `get_ca1` int(11) DEFAULT NULL,
  `get_ca2` int(11) DEFAULT NULL,
  `get_ca3` int(11) DEFAULT NULL,
  `get_ca4` int(11) DEFAULT NULL,
  `get_ca5` int(11) DEFAULT NULL,
  `get_ca6` int(11) DEFAULT NULL,
  `get_exam` int(11) DEFAULT NULL,
  `get_tot_score` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `expected` text DEFAULT NULL,
  `achieved` text DEFAULT NULL,
  `remark` text DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL,
  `exam_group_student_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `exam_group_exam_results`
--

INSERT INTO `exam_group_exam_results` (`id`, `exam_group_class_batch_exam_student_id`, `exam_group_class_batch_exam_subject_id`, `attendence`, `get_marks`, `get_ca1`, `get_ca2`, `get_ca3`, `get_ca4`, `get_ca5`, `get_ca6`, `get_exam`, `get_tot_score`, `note`, `expected`, `achieved`, `remark`, `is_active`, `created_at`, `updated_at`, `exam_group_student_id`) VALUES
(1, 1, 1, 'present', NULL, 7, 10, 10, 10, 10, 10, 34, 91, NULL, NULL, NULL, NULL, 0, '2022-06-20 15:04:54', NULL, NULL),
(2, 2, 1, 'present', NULL, 5, 6, 8, 5, 3, 9, 26, 62, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(3, 3, 1, 'present', NULL, 8, 9, 9, 6, 5, 9, 39, 85, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(4, 1, 2, 'present', NULL, 9, 7, 6, 8, 9, 6, 32, 77, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(5, 2, 2, 'present', NULL, 7, 8, 6, 9, 9, 6, 38, 83, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(6, 3, 2, 'present', NULL, 7, 6, 9, 6, 5, 7, 28, 68, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(7, 4, 3, 'present', NULL, 7, 9, 7, 4, 5, 6, 32, 70, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(8, 5, 3, 'present', NULL, 6, 5, 7, 8, 8, 6, 37, 77, NULL, NULL, NULL, NULL, 0, '2022-06-20 11:48:49', NULL, NULL),
(13, 6, 6, 'present', NULL, 7, 8, 6, 5, 7, 9, 39, 81, NULL, NULL, NULL, NULL, 0, '2022-06-20 15:09:02', NULL, NULL),
(14, 7, 6, 'present', NULL, 9, 9, 9, 9, 9, 9, 35, 89, NULL, NULL, NULL, NULL, 0, '2022-06-20 15:09:02', NULL, NULL);

This is my exam_group_class_batch_exam_subjects table (I don’t know if this is actually necessary but here it is)

CREATE TABLE `exam_group_class_batch_exam_subjects` (
  `id` int(11) NOT NULL,
  `exam_group_class_batch_exams_id` int(11) DEFAULT NULL,
  `subject_id` int(10) NOT NULL,
  `date_from` date NOT NULL,
  `time_from` time NOT NULL,
  `duration` varchar(50) NOT NULL,
  `room_no` varchar(100) DEFAULT NULL,
  `max_marks` float(10,2) DEFAULT NULL,
  `min_marks` float(10,2) DEFAULT NULL,
  `credit_hours` float(10,2) DEFAULT 0.00,
  `date_to` datetime DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `exam_group_class_batch_exam_subjects`
--

INSERT INTO `exam_group_class_batch_exam_subjects` (`id`, `exam_group_class_batch_exams_id`, `subject_id`, `date_from`, `time_from`, `duration`, `room_no`, `max_marks`, `min_marks`, `credit_hours`, `date_to`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 1, 1, '2022-06-09', '20:20:51', '0', '', 100.00, 50.00, 0.00, NULL, 0, '2022-06-10 19:21:29', NULL),
(2, 1, 2, '2022-06-09', '20:20:58', '0', '', 100.00, 50.00, 0.00, NULL, 0, '2022-06-10 19:21:29', NULL),
(3, 2, 1, '2022-06-15', '17:11:09', '0', '1', 100.00, 50.00, 0.00, NULL, 0, '2022-06-15 16:11:29', NULL),
(4, 5, 2, '2022-06-16', '12:30:38', '0', '1', 100.00, 50.00, 0.00, NULL, 0, '2022-06-16 11:39:05', NULL),
(5, 6, 1, '2022-06-16', '18:40:28', '0', '', 100.00, 50.00, 0.00, NULL, 0, '2022-06-16 17:40:40', NULL),
(6, 7, 1, '2022-06-16', '22:42:39', '0', '', 100.00, 50.00, 0.00, NULL, 0, '2022-06-16 21:42:49', NULL),
(7, 8, 2, '2022-06-16', '22:56:36', '0', '', 100.00, 50.00, 0.00, NULL, 0, '2022-06-16 21:56:48', NULL);

Leave a Comment