0

So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will then be added together to give a total which will be used to do the ranking of the students.

I used the query below but it is not using the right averages as it doesn't group the types correctly. For example.. where it should get the average for all grades that are not exam it also includes exam grades:

SELECT * FROM 
(SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank
FROM (SELECT student_id, 
((SELECT AVG(grade) where grades.type != 'exam')/100 * 40 ) as avg, 
((SELECT AVG(grade) where type = 'exam')/100 * 60 ) as exam FROM grades 
WHERE class_id = 1 AND subject_id = 1 GROUP BY student_id ) agg 
CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc) t 

See a screenshot of the table below: enter image description here

See the Query to create table below:

Table structure for table grades

CREATE TABLE `grades` (
  `grade_id` int(11) NOT NULL,
  `assessment_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `grade` double NOT NULL,
  `teacher_comment` text,
  `type` enum('Home Work','Class','Unit Test','Assignment','Exam') DEFAULT NULL,
  `term` enum('1','2','3') DEFAULT NULL,
  `sch_year` year(4) NOT NULL,
  `entered_by` int(11) DEFAULT NULL,
  `enter_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `grades`
--

INSERT INTO `grades` (`grade_id`, `assessment_id`, `subject_id`, `student_id`, `class_id`, `grade`, `teacher_comment`, `type`, `term`, `sch_year`, `entered_by`, `enter_date`) VALUES
(1, 1, 3, 10001, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:04:38'),
(5, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(6, 1, 3, 10002, 2, 60, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(7, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(8, 1, 3, 10002, 2, 75, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(9, 1, 3, 10003, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(10, 1, 3, 10001, 2, 85, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(11, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(12, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:54'),
(13, 1, 3, 10001, 2, 98, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(14, 1, 3, 10002, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(15, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(16, 1, 3, 10001, 2, 96, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(17, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(18, 1, 3, 10003, 2, 86, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(22, 3, 4, 10001, 2, 55, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(23, 3, 4, 10002, 2, 75, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(24, 3, 4, 10003, 2, 85, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(25, 4, 3, 10001, 2, 85, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(26, 4, 3, 10002, 2, 90, 'Test', 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(27, 4, 3, 10003, 2, 65, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(28, 5, 3, 10001, 2, 96, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(29, 5, 3, 10002, 2, 90, 'Test', 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(30, 5, 3, 10003, 2, 98, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(31, 6, 2, 10001, 2, 55, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(32, 6, 2, 10002, 2, 65, 'Test', 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(33, 6, 2, 10003, 2, 60, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(34, 10, 3, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(35, 10, 3, 10002, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(36, 10, 3, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(37, 9, 1, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(38, 9, 1, 10002, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(39, 9, 1, 10003, 2, 93, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(40, 12, 2, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(41, 12, 2, 10002, 2, 96, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(42, 12, 2, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(43, 11, 3, 10001, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(44, 11, 3, 10002, 2, 88, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(45, 11, 3, 10003, 2, 92, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(46, 13, 4, 10001, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(47, 13, 4, 10002, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(48, 13, 4, 10003, 2, 52, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(49, 13, 4, 10004, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(50, 13, 4, 10005, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(51, 13, 4, 10006, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(52, 13, 4, 10007, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(53, 13, 4, 10008, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(54, 13, 4, 10009, 2, 58, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(55, 13, 4, 10010, 2, 97, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(56, 13, 4, 10011, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(57, 13, 4, 10024, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(58, 13, 4, 10012, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(59, 13, 4, 10013, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(60, 13, 4, 10014, 2, 54, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(61, 13, 4, 10015, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(62, 13, 4, 10016, 2, 86, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(63, 13, 4, 10017, 2, 57, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(64, 13, 4, 10018, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(65, 13, 4, 10019, 2, 93, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(66, 13, 4, 10020, 2, 99, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(67, 13, 4, 10021, 2, 72, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(68, 13, 4, 10022, 2, 100, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(69, 13, 4, 10023, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(70, 13, 4, 10025, 2, 95, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(71, 13, 4, 10026, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(72, 13, 4, 10027, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(73, 14, 5, 10001, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(74, 14, 5, 10002, 2, 53, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(75, 14, 5, 10003, 2, 21, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(76, 14, 5, 10004, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(77, 14, 5, 10005, 2, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(78, 14, 5, 10006, 2, 55, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(79, 14, 5, 10007, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(80, 14, 5, 10008, 2, 89, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(81, 14, 5, 10009, 2, 93, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(82, 14, 5, 10010, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(83, 14, 5, 10011, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(84, 14, 5, 10024, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(85, 14, 5, 10012, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(86, 14, 5, 10013, 2, 80, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(87, 14, 5, 10014, 2, 97, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(88, 14, 5, 10015, 2, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(89, 14, 5, 10016, 2, 45, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(90, 14, 5, 10017, 2, 77, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(91, 14, 5, 10018, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(92, 14, 5, 10019, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(93, 14, 5, 10020, 2, 87, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(94, 14, 5, 10021, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(95, 14, 5, 10022, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(96, 14, 5, 10023, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(97, 14, 5, 10025, 2, 82, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(98, 14, 5, 10026, 2, 78, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(99, 14, 5, 10027, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(100, 15, 5, 10001, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(101, 15, 5, 10002, 2, 90, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(102, 15, 5, 10003, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(103, 15, 5, 10004, 2, 55, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(104, 15, 5, 10005, 2, 85, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(105, 15, 5, 10006, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(106, 15, 5, 10007, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(107, 15, 5, 10008, 2, 58, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(108, 15, 5, 10009, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(109, 15, 5, 10010, 2, 75, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(110, 15, 5, 10011, 2, 77, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(111, 15, 5, 10024, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(112, 15, 5, 10012, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(113, 15, 5, 10013, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(114, 15, 5, 10014, 2, 79, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(115, 15, 5, 10015, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(116, 15, 5, 10016, 2, 83, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(117, 15, 5, 10017, 2, 80, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(118, 15, 5, 10018, 2, 84, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(119, 15, 5, 10019, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(120, 15, 5, 10020, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(121, 15, 5, 10021, 2, 78, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(122, 15, 5, 10022, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(123, 15, 5, 10023, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(124, 15, 5, 10025, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(125, 15, 5, 10026, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(126, 15, 5, 10027, 2, 95, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(127, 16, 1, 10028, 1, 85, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(128, 16, 1, 10029, 1, 88, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(129, 16, 1, 10030, 1, 89, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(130, 17, 1, 10028, 1, 90, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(131, 17, 1, 10029, 1, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(132, 17, 1, 10030, 1, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `grades`
--
ALTER TABLE `grades`
  ADD PRIMARY KEY (`grade_id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `class_id` (`class_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `grades`
--
ALTER TABLE `grades`
  MODIFY `grade_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `grades`
--
ALTER TABLE `grades`
  ADD CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  • Could you give us the output of SHOW CREATE TABLE grades\G and also a mysqldump of your data rather than a graphic? Typing all that it would be a real drag! :-) – Vérace Jul 17 '16 at 14:20
  • I have edited to question. – DBeck Jul 17 '16 at 15:13
2

The query you posted is actually running without syntax errors?

You need an average over a CASE like this:

SELECT * 
FROM 
 ( SELECT student_id, avg, 
      exam,(exam) + (avg) as total,
      (@rn := @rn + 1)  AS rank
   FROM
    ( SELECT student_id, 
         AVG(CASE WHEN type <> 'exam' THEN grade END) / 100 * 40 as avg, 
         AVG(CASE WHEN type  = 'exam' THEN grade END) / 100 * 60 as exam 
       FROM grades 
       WHERE class_id = 1
         AND subject_id = 1
       GROUP BY student_id
    ) agg 
   CROSS JOIN (SELECT @rn := 0) CONST 
   ORDER BY total desc
 ) t
  • This query gave a lot of miscalculation problems. I have added the solution below. Thanks for the help though. – DBeck Jul 19 '16 at 1:36
  • @DBeck: The wrong result was due to the ELSE 0, of course this is changing the average. – dnoeth Jul 19 '16 at 6:12
0

Here is the solution to the problem:

SELECT * FROM 

(

SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank 
FROM 
(SELECT student_id, 
    (
        SELECT AVG(g.grade)  / 100 * 40 
        FROM grades AS g
        WHERE g.type != 'Exam' AND  g.student_id = grades.student_id
    ) as avg,
    (
        SELECT AVG(s.grade)/100 * 60 
        FROM grades AS s
        WHERE s.type = 'Exam' AND  s.student_id = grades.student_id
    ) as exam 
FROM grades 
WHERE class_id = 2 AND subject_id = 1 GROUP BY student_id ) agg
CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc

) t

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.