delimiter |;

# aggregate AND function

create aggregate function agg_and(x INT) returns INT
begin
   declare z double default 1000;
   declare continue handler for not found return z;
   loop
   fetch group next row;
   set z= (z&x);
   end loop;
end|
 
delimiter ;|
 
create table t1 (sal int, id int, val int, counter int);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 1, 16, 5);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 5, 10, 7);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 7, 13, 8);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 6, 19, 9);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 7, 12, 0);
INSERT INTO t1 (sal, id, val, counter) VALUES (4000, 6, 14, 6);
INSERT INTO t1 (sal, id, val, counter) VALUES (8000, 5, 19, 11);
INSERT INTO t1 (sal, id, val, counter) VALUES (9000, 4, 11, 12);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 11, 13);
 
select agg_and(sal) from t1 where id>= 1;
 
delimiter |;
 
# aggregate AVG function
 
create aggregate function agg_avg(x INT) returns double
begin
  declare z double default 0;
  declare count double default 0;
  declare continue handler for not found return z/count;
  loop
   fetch group next row;
   set z= z+x;
   set count= count+1;
  end loop;
end|
 
delimiter ;|
select agg_avg(sal) from t1 where id>= 1;
delimiter |;
 
# aggregate MAX function
 
create aggregate function agg_max(x INT) returns INT
begin
  declare maxi INT default 1;
  declare continue handler for not found return maxi;
  loop
   fetch group next row;
   if maxi < x then
      set maxi= x;
   end if;
  end loop;
end|
 
delimiter ;|
select agg_max(sal) from t1 where id>= 1;
delimiter |;
 
# aggregate MIN function
 
create aggregate function agg_min(x INT) returns double
begin
  declare mini INT default 100000;
  declare continue handler for not found return mini;
  loop
   fetch group next row;
   if mini > x then
     set mini = x;
   end if;
  end loop;
end|
 
 delimiter ;|
 select agg_min(sal) from t1 where id>= 1;
 delimiter |;
 
# aggregate XOR function
 
create aggregate function agg_xor(x INT) returns double
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
   fetch group next row;
   set z= z^x;
  end loop;
end|
 
delimiter ;|
select agg_xor(sal) from t1 where id>= 1;
delimiter |;
 
# aggregate SUM function
 
create aggregate function agg_sum(x INT) returns INT
begin
  declare z int default 0;
  declare continue handler for not found return z;
  loop
   fetch group next row;
   set z= z+x;
  end loop;
 end|
 
delimiter ;|
select agg_sum(sal) from t1 where id>= 1;
delimiter |;
 
# aggregate INCREM function

create aggregate function agg_increm() returns INT
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
   fetch group next row;
   set z= z+1;
  end loop;
end|
 
delimiter ;|
 
# no parameters
select agg_increm() from t1;
 
create table t2 (sal int, id int);
INSERT INTO t2 (sal, id) VALUES (NULL, 1);
INSERT INTO t2 (sal, id) VALUES (2000, 1);
INSERT INTO t2 (sal, id) VALUES (3000, 1);
 
# null values
select agg_sum(sal) from t2;

# no tables
select agg_sum(1);

# aggregate function called from regular functions
create function f1() returns int
return (select agg_sum(sal) from t1);
select f1();
 
create function increase() returns INT
return 1;
 
# regular functions called from aggregate functions
delimiter |;
create aggregate function agg_reg_func() returns INT
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
   fetch group next row;
   set z= z+f1();
  end loop;
end|
 
delimiter ;|
select agg_reg_func() from t2;
delimiter |;
 
# aggregate functions called from aggregate functions
create aggregate function agg_agg_func(x INT) returns INT
begin
  declare z int default 0;
  declare continue handler for not found return z;
  loop
   fetch group next row;
   if x then
      set z= z+(select agg_sum(sal) from t1);
   end if;
  end loop;
end|
 
delimiter ;|
select agg_agg_func(sal) from t2;
 
# GROUP BY AND ORDER BY
--sorted_result
select id, agg_sum(sal) from t1 where id>= 1 group by id;
--sorted_result
select val, agg_sum(sal) from t1 where id>= 1 group by val;
select counter, agg_sum(sal) from t1 where id>= 1 group by id order by counter;
select counter, id, agg_sum(sal), agg_sum(sal) from t1 where id>= 1 group by id order by counter;
select counter, id, agg_sum(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc;
select val, counter, id, agg_sum(sal), sum(distinct sal), agg_min(sal), agg_max(sal), agg_avg(sal) from t1 where id>= 1 group by id, val order by counter, val;
 
##### insert aggregate function value into a table ######
create table t3 (i int);
INSERT INTO t3 (i) select agg_sum(sal) from t1;
select * from t3;
 
delimiter |;
 
create aggregate function agg_func(x INT) returns INT
begin
  declare z int default 0;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE a,b,c  INT;
  DECLARE cur1 CURSOR FOR SELECT id FROM test.t2;
  declare continue handler for not found return z;
 
    outer_loop: LOOP
       FETCH GROUP NEXT ROW;
       set z= z+x;
         inner_block: begin
         DECLARE cur2 CURSOR FOR SELECT id FROM test.t2;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
         OPEN cur2;
 
         read_loop: LOOP
          FETCH cur2 INTO a;
          IF done THEN
           CLOSE cur2;
           LEAVE read_loop;
          END IF;
         END LOOP read_loop;

      end inner_block;
    END LOOP outer_loop;
 
end|
 
delimiter ;|
select agg_func(sal) from t1; 
drop table t1;
drop table t2;
drop table t3;
drop function agg_sum;
drop function agg_min;
drop function agg_max;
drop function agg_avg;
drop function agg_xor;
drop function agg_and;
drop function agg_increm;
drop function f1;
drop function increase;
drop function agg_reg_func;
drop function agg_agg_func;
drop function agg_func;