I have no idea of a databases and SQL queries. I have a database table like this:
+-----+------------+---------------+-------------------------+
| id | server_id | secret_number | ts |
+-----+------------+---------------+-------------------------+
| 161 | R_SERVER | 1 | 2023-06-22 15:04:09.549 |
| 162 | X_R_SERVER | 49 | 2023-06-22 15:04:10.570 |
| 163 | R_SERVER | 2 | 2023-06-22 15:04:11.574 |
| 164 | X_R_SERVER | 48 | 2023-06-22 15:04:12.584 |
| 165 | R_SERVER | 3 | 2023-06-22 15:04:13.588 |
| 166 | X_R_SERVER | 47 | 2023-06-22 15:04:14.602 |
| 167 | R_SERVER | 4 | 2023-06-22 15:04:15.610 |
| 168 | X_R_SERVER | 46 | 2023-06-22 15:04:16.616 |
| 169 | R_SERVER | 5 | 2023-06-22 15:04:17.628 |
| 170 | X_R_SERVER | 45 | 2023-06-22 15:04:18.637 |
| 171 | R_SERVER | 6 | 2023-06-22 15:04:19.641 |
| 172 | X_R_SERVER | 44 | 2023-06-22 15:04:20.648 |
| 173 | R_SERVER | 7 | 2023-06-22 15:04:21.658 |
| 174 | X_R_SERVER | 43 | 2023-06-22 15:04:22.669 |
| 175 | R_SERVER | 8 | 2023-06-22 15:04:23.673 |
| 176 | X_R_SERVER | 42 | 2023-06-22 15:04:24.683 |
| 177 | R_SERVER | 9 | 2023-06-22 15:04:25.685 |
| 178 | X_R_SERVER | 41 | 2023-06-22 15:04:26.690 |
| 179 | R_SERVER | 10 | 2023-06-22 15:04:27.696 |
| 180 | X_R_SERVER | 40 | 2023-06-22 15:04:28.704 |
| 181 | R_SERVER | 11 | 2023-06-22 15:04:29.713 |
| 182 | X_R_SERVER | 39 | 2023-06-22 15:04:30.724 |
| 183 | R_SERVER | 12 | 2023-06-22 15:04:31.734 |
| 184 | X_R_SERVER | 38 | 2023-06-22 15:04:32.744 |
| 185 | R_SERVER | 13 | 2023-06-22 15:04:33.754 |
| 186 | X_R_SERVER | 37 | 2023-06-22 15:04:34.762 |
+-----+------------+---------------+-------------------------+
The id field is an AUTO_INCREMENT PRIMARY KEY. I am trying to write a query that will give me the SUM of secret_number for a given server, let's say R_SERVER in a group of 5 rows ordered by id. And when the number of left over rows are less than 5, those rows should be ignored. For example, the output should be like this:
server_id sum
R_SERVER 15 (for ids 161, 163, 165, 167, 169)
40 (for next 5 ids belonging to R_SERVER)
0 or excluded (for the left over ids since they not yet 5)
X_R_SERVER 235 (for ids 162, 164, 166, 168, 170)
210 (for next 5 ids belonging to X_R_SERVER)
0 or excluded (for the left over ids since they not yet 5)
If it is difficult to fetch R_SERVER and X_R_SERVER details in a single query, 2 queries for each of them is also Ok.
The query below uses the row_number function to create a series of incremented IDs for each series of server_ids. Then, the summing of secret_numbers for each server_id is performed by grouping on an adjustment of that ID (aliased as r), produced by finding all prior occurrences of 0 ≡ r mod 5 (via a subquery):
with cte as (
select row_number() over (partition by s.server_id order by s.server_id, s.id) r, s.*
from servers s
)
select t1.server_id, t1.s from (select t.server_id, t.k,
sum(t.secret_number) s, count(*) c
from (select c.r, c.id, c.server_id, c.secret_number, (select sum(c1.r < c.r and c1.r % 5 = 0)
from cte c1 where c1.server_id = c.server_id) k
from cte c order by c.server_id, c.id
) t group by t.server_id, t.k) t1 where t1.c = 5