Iterate through query

I’m trying to iterate through simple query

select a_id, count(ID), TIMESTAMPDIFF(SECOND,MIN(`Time`),MAX(`Time`)) 
from data
where a_id = 1

This is what I managed to create

    DECLARE counter INT DEFAULT 0;

   WHILE counter < 10 
    BEGIN
        
    select a_id, count(ID), TIMESTAMPDIFF(SECOND,MIN(`Time`),MAX(`Time`)) 
    from data 
    where a_id = counter
    
     SET counter = counter + 1;
   END

But I get:

    Error occurred during SQL script execution

Reason:
SQL Error [1064] [42000]: (conn=1956) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE counter INT DEFAULT 0' at line 1

Answer

You don’t need a while loop for this. You can do this in a single query. One method is:

with recursive ids as (
      select 1 as id
      union all
      select id + 1
      from ids
      where id < 10
     )
select d.a_id, count(*)
       timestampdiff(second, min(d.`Time`), min(d.`Time`)) 
from ids left join
     data d
     on d.a_id = ids.id
order by ids.id;

Trying to use a while look is troublesome. Such programming constructs would be in programming blocks. They are designed for stored procedures, functions, and triggers.

I assume that your code is actually over-simplified, because this probably does what you want:

select d.a_id, count(*)
       timestampdiff(second, min(d.`Time`), min(d.`Time`)) 
from data d
where a.id between 1 and 10
group by d.a_id