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

ghz 8months ago ⋅ 49 views

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!

Answers

Your approach is close, but there are a couple of issues in your function. Here's a corrected version:

DELIMITER //

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

  -- Declare variables to hold the maximum sum amount and the corresponding animal ID
  DECLARE max_sum_amount INT(10) DEFAULT 0;
  DECLARE animal_id_with_max_amount INT(3) DEFAULT 0;

  -- Select the sum of amounts donated to each animal by the specified donor
  SELECT animal_id, SUM(amount) AS sum_amount
  INTO animal_id, sum_amount
  FROM donations
  WHERE donor_id = p_donor_id
  GROUP BY animal_id;

  -- Loop through the results to find the animal with the maximum sum amount
  SELECT MAX(sum_amount) INTO max_sum_amount FROM (
      SELECT SUM(amount) AS sum_amount
      FROM donations
      WHERE donor_id = p_donor_id
      GROUP BY animal_id
  ) AS subquery;

  -- Select the animal ID corresponding to the maximum sum amount
  SELECT animal_id INTO animal_id_with_max_amount
  FROM (
      SELECT animal_id, SUM(amount) AS sum_amount
      FROM donations
      WHERE donor_id = p_donor_id
      GROUP BY animal_id
  ) AS subquery
  WHERE sum_amount = max_sum_amount;

  -- Set the favorite animal to the one with the maximum sum amount
  SET favAnimal = animal_id_with_max_amount;

  RETURN favAnimal;
END //

DELIMITER ;

Here's what I've changed:

  1. I added variables max_sum_amount and animal_id_with_max_amount to keep track of the maximum sum amount and its corresponding animal ID.
  2. I modified the SELECT INTO statement to populate these variables with the animal ID and sum amount for each animal.
  3. I added a subquery to find the maximum sum amount.
  4. I used another subquery to find the animal ID corresponding to the maximum sum amount.
  5. Finally, I set favAnimal to the animal ID with the maximum sum amount and returned it.
#sql