Stored Aggregate Functions

Aggregate functions are functions that are computed over a sequence of rows and return one result for the sequence of rows.

Creating a custom aggregate function is done using the CREATE FUNCTION statement with two main differences:

• The addition of the AGGREGATE keyword, so CREATE AGGREGATE FUNCTION
• The FETCH GROUP NEXT ROW instruction inside the loop.

Simplified Syntax

CREATE AGGREGATE FUNCTION function_name (parameters) RETURNS return_type  
BEGIN  
      All types of declarations  
      DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN return_val;  
      LOOP  
           FETCH GROUP NEXT ROW; // fetches next row from table
           other instructions 
      END LOOP; 
END 

Examples

First a simplified example:

CREATE TABLE marks(stud_id INT, grade_count INT); 

INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8);

SELECT * FROM marks;
+---------+-------------+
| stud_id | grade_count |
+---------+-------------+
|       1 |           6 |
|       2 |           4 |
|       3 |           7 |
|       4 |           5 |
|       5 |           8 |
+---------+-------------+

DELIMITER //
CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT  
BEGIN  
 DECLARE count_students INT DEFAULT 0;  
 DECLARE CONTINUE HANDLER FOR NOT FOUND
 RETURN count_students;       
      LOOP  
          FETCH GROUP NEXT ROW;  
          IF x  THEN  
            SET count_students = count_students+1;  
          END IF;  
      END LOOP;  
END //
DELIMITER ;