Mysql Question posted in
The official MySQL documentation can be found here.

Is it possible to return the MAX of a SUM in a stored function? MYSQL

I am working with a database for an animal sanctuary where people can make donations to specific animals. I am trying to create a function that would return which animal a donor has donated to the most. The table I am working on is called donations and the columns are donation_id, donor_id, animal_id, amount, and date_donated.

I have tried this query(and alterations of it) but continuously have gotten errors. I am hoping to have the function return favAnimal as the animal_id of the animal the selected donor has donated to the most.

DELIMITER //
CREATE FUNCTION FindFavAnimal(p_donor_id INT(3))
RETURNS INT(3)
BEGIN
  DECLARE favAnimal INT(3)

  SELECT SUM(amount) AS sum_amount
  FROM donations
  WHERE donor_id = p_donor_id
  GROUP BY animal_id;

  SET favAnimal = MAX(sum_amount);
   
   RETURN favAnimal;
END //
DELIMITER ;

Any help is greatly appreciated!

Tags:

2

Answers


  1. 0

    you were almost there. since you said only need the animal_id of the animal a selected donor has donated to the most, you do not need to return the SUM(amount) in your outer query. try this

    DELIMITER //
CREATE FUNCTION FindFavAnimal(p_donor_id INT)
RETURNS INT
BEGIN
    DECLARE favAnimal INT;
    DECLARE maxAmount INT;

    -- Get the maximum sum of donations for the given donor
    SELECT MAX(sum_amount) INTO maxAmount
    FROM (
        SELECT SUM(amount) AS sum_amount
        FROM donations
        WHERE donor_id = p_donor_id
        GROUP BY animal_id
    ) AS donation_sums;

    -- Get the animal_id corresponding to the maximum sum
    SELECT animal_id INTO favAnimal
    FROM (
        SELECT animal_id, SUM(amount) AS sum_amount
        FROM donations
        WHERE donor_id = p_donor_id
        GROUP BY animal_id
    ) AS animal_donation_sums
    WHERE sum_amount = maxAmount;

    RETURN favAnimal;
END //
DELIMITER ;
    Login or Signup to reply.
  2. 0 
    CREATE FUNCTION FindFavAnimal(p_donor_id INT)
RETURNS INT
RETURN ( SELECT SUM(amount) AS sum_amount
         FROM donations
         WHERE donor_id = p_donor_id
         GROUP BY animal_id 
         ORDER BY 1 DESC LIMIT 1
       );

    Neither BEGIN-END nor DELIMITER needed.

    PS. Your function name seems to be not correct – you return maximal donation sum, not according animal ID/name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search