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`, `aicicc_lastposition`, `aicicc_status`, `pan_percentile`, `age_percentile`, `created_at`, `updated_at`) VALUES
(270, 3, 14, 17, 7, 'Time', 50, 1, 5, 'Active', NULL, NULL, '2022-11-15 08:34:40', '2022-11-15 08:34:40'),
(271, 3, 14, 20, 7, 'Time', 60, 1, 231, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 08:35:21'),
(272, 3, 14, 21, 7, 'Time', 70, 1, 20, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 08:35:21'),
(273, 3, 14, 17, 7, 'Time', 90, 2, 5, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 12:13:42'),
(274, 3, 14, 20, 7, 'Time', 40, 2, 231, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 08:35:21'),
(275, 3, 14, 21, 7, 'Time', 70, 2, 20, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 08:35:21'),
(276, 3, 10, 17, 3, 'Time', 80, 1, 5, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 12:10:25'),
(277, 3, 10, 20, 3, 'Time', 60, 1, 231, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 12:10:43'),
(278, 3, 10, 21, 3, 'Time', 60, 1, 20, 'Active', NULL, NULL, '2022-11-15 08:34:45', '2022-11-15 12:11:03');

I need 3 rows form this table with average like this

my query is

SELECT DISTINCT at_id, AVG(aicicc_tp) OVER (PARTITION BY aicicc_attempt) as average
FROM iot_athlete_ind_cont_non_cur_chlng_consp
WHERE aicid_id = '3';

but its not working properly average calculation is wrong here in my query.

CodePudding user response：

Let's first say that's basically just a combination of average and GROUP BY:

SELECT at_id, aicicc_attempt, 
AVG(aicicc_tp) AS average
FROM iot_athlete_ind_cont_non_cur_chlng_consp
GROUP BY at_id, aicicc_attempt
ORDER BY at_id DESC;

(I don't know if the ORDER BY clause is necessary, otherwise the last row appears as first row).

The "problem" might be that the "average" column will not exactly be shown as you wanted. For example, let's assume the column "aicicc_tp" has been declared as data type int and you are using a SQLServer DB. In this case, your outcome will also show the average as integer:

You will need to cast the column as float and format the outcome to generate exactly the desired result (of course, the correct average for your sample data is 66.67, not 66.66):

SELECT at_id, aicicc_attempt, 
FORMAT(AVG(CAST(aicicc_tp AS float)), '##.##') AS average
FROM iot_athlete_ind_cont_non_cur_chlng_consp
GROUP BY at_id, aicicc_attempt
ORDER BY at_id DESC;

If you are using another DB type, the concrete query will differ. That's why it's recommended to tag your DB type.

EDIT because you changed the question: Adding the WHERE clause that is now mentioned in your question does not change the result:

SELECT at_id, aicicc_attempt, 
FORMAT(AVG(CAST(aicicc_tp AS float)), '##.##') AS average
FROM iot_athlete_ind_cont_non_cur_chlng_consp
WHERE aicid_id = 3
GROUP BY at_id, aicicc_attempt
ORDER BY at_id DESC;

The outcome is still the same and correct:

And a last note: If you want to keep your PARTITION BY idea, your query must be like this:

SELECT DISTINCT at_id, aicicc_attempt, 
AVG(aicicc_tp)
OVER (PARTITION BY at_id, aicicc_attempt) AS average
FROM iot_athlete_ind_cont_non_cur_chlng_consp
WHERE aicid_id = 3
ORDER BY at_id DESC;

But I don't recommend this because of some disadvantages:

1. The usage of DISTINCT often slows down the query.
2. This will not solve the issue the average is possibly built as integer which is not intended according to your description.
3. Window functions often differ depending on the DB type, GROUP BY works the same on each DB type.

You can verify all these things here: