INSERT INTO `iot_athlete_ind_cont_non_cur_chlng_consp` (`aicicc_id`, `aicid_id`, `aicidl_id`, `aica_id`, `at_id`, `aicicc_type`, `aicicc_tp`, `aicicc_attempt`, `pan_percentile`, `age_percentile`, `aicicc_lastposition`, `aicicc_status`, `created_at`, `updated_at`) VALUES
(198, 414, 709, 8, 263, 'Time', '25', 1, NULL, NULL, 12, 'Active', '2023-04-25 11:31:22', '2023-04-25 11:57:28'),
(199, 414, 709, 69, 263, 'Reps', '22', 1, NULL, NULL, 33, 'Active', '2023-04-25 11:32:18', '2023-04-25 11:57:44'),
(200, 414, 709, 8, 263, 'Time', '21', 2, NULL, NULL, 12, 'Active', '2023-04-25 11:33:02', '2023-04-25 11:57:48'),
(201, 414, 709, 69, 263, 'Reps', '36', 2, NULL, NULL, 33, 'Active', '2023-04-25 11:33:24', '2023-04-25 11:57:52'),
(202, 414, 709, 8, 263, 'Time', '26', 3, NULL, NULL, 12, 'Active', '2023-04-25 11:33:42', '2023-04-25 11:58:01'),
(203, 414, 709, 69, 263, 'Reps', '20', 3, NULL, NULL, 33, 'Active', '2023-04-25 11:33:54', '2023-04-25 11:58:07'),
(204, 414, 709, 8, 263, 'Time', '19', 4, NULL, NULL, 12, 'Active', '2023-04-25 11:34:08', '2023-04-25 11:58:13'),
(205, 414, 709, 69, 263, 'Reps', '16', 4, NULL, NULL, 33, 'Active', '2023-04-25 11:34:21', '2023-04-25 11:58:18'),
(206, 414, 709, 8, 263, 'Time', '13', 5, NULL, NULL, 12, 'Active', '2023-04-25 11:34:43', '2023-04-25 11:58:22'),
(207, 414, 709, 69, 263, 'Reps', '33', 5, NULL, NULL, 33, 'Active', '2023-04-25 11:34:54', '2023-04-25 11:58:27'),
(208, 414, 710, 8, 276, 'Time', '3', 1, NULL, NULL, 12, 'Active', '2023-04-25 11:37:13', '2023-04-25 11:54:25'),
(209, 414, 710, 69, 276, 'Reps', '56', 1, NULL, NULL, 33, 'Active', '2023-04-25 11:39:51', '2023-04-25 11:54:32'),
(210, 414, 710, 8, 276, 'Time', '22', 2, NULL, NULL, 12, 'Active', '2023-04-25 11:40:56', '2023-04-25 11:54:45'),
(211, 414, 710, 69, 276, 'Reps', '20', 2, NULL, NULL, 33, 'Active', '2023-04-25 11:41:09', '2023-04-25 11:55:13'),
(212, 414, 710, 8, 276, 'Time', '21', 3, NULL, NULL, 12, 'Active', '2023-04-25 11:41:19', '2023-04-25 11:55:18'),
(213, 414, 710, 69, 276, 'Reps', '25', 3, NULL, NULL, 33, 'Active', '2023-04-25 11:41:31', '2023-04-25 11:55:24'),
(214, 414, 710, 8, 276, 'Time', '20', 4, NULL, NULL, 12, 'Active', '2023-04-25 11:41:44', '2023-04-25 11:55:28'),
(215, 414, 710, 69, 276, 'Reps', '26', 4, NULL, NULL, 33, 'Active', '2023-04-25 11:41:58', '2023-04-25 11:55:33'),
(216, 414, 710, 8, 276, 'Time', '50', 5, NULL, NULL, 12, 'Active', '2023-04-25 11:42:25', '2023-04-25 11:55:45'),
(217, 414, 710, 69, 276, 'Reps', '49', 5, NULL, NULL, 33, 'Active', '2023-04-25 11:42:35', '2023-04-25 11:55:49');
I need attempt(aicicc_attempt) wise average.
value for calculation of average=aicicc_tp;
where at_id=263 aica_id=8 and 69 and aicicc_attempt=1,(25+22)=average will be 23.5
again where at_id=263 aica_id=8 and 69 and aicicc_attempt=2, (21+36)=average will be 57
again where at_id=263 aica_id=8 and 69 and aicicc_attempt=3, (26+20)=average will be 23
again where at_id=263 aica_id=8 AND 69 and aicicc_attempt=4, (19+16)=average will be 17.5
again where at_id=263 aica_id=8 AND 69 and aicicc_attempt=5, (13+33)=average will be 23
for multiple at_id;
where at_id=276 aica_id=8 and 69 and aicicc_attempt=1, (3+56)=average will be 29.5
again where at_id=276 aica_id=8 and 69 and aicicc_attempt=2, (22+20)=average will be 21
again where at_id=276 aica_id=8 and 69 and aicicc_attempt=3, (21+25)=average will be 23
again where at_id=276 aica_id=8and 69 and aicicc_attempt=4, (20+26)=average will be 23
again where at_id=276 aica_id=8and 69 and aicicc_attempt=5, (50+49)=average will be 49.5
I am using this query for calculation of percentage-I need one more column for average.
SELECT *,percent_rank() OVER (ORDER BY convert(aicicc_tp, decimal) ASC ) AS prcnt_rank FROM iot_athlete_ind_cont_non_cur_chlng_consp where aicid_id = ‘414’ ORDER BY iot_athlete_ind_cont_non_cur_chlng_consp.aicicc_attempt ASC;