Not allowed to return a result set from a trigger for the query I ran

I want to create a trigger which updates my ‘ratio’ column automatically within the same table. I need to sort by date(month and year) for the specific items get the sum for the items at that date then divide by the item values.

This is the query I tried:

create trigger ratio
before update
ON Table1
for each row
(select Month,Year,sum(item_value) as sum_item
    from Table1
    where item_name like ('BUC%')
    group by Month,Year) x
on Table1.Month = x.Month and Table1.Year = x.Year and Table1.item_name like ('BUC%')
set Table1.Ratio = Table1.item_value/x.sum_item;

but I get the error Error Code: 1415. Not allowed to return a result set from a trigger which I kind of understand. I’m very new to mysql by the way.

If I can’t do what I want this way is there another way or method to get it done?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use a subquery to get the total value for the same product and month, and use that to calculate the ratio for the updated row.

DELIMITER $$

create trigger ratio
before update
ON Table1
for each row
IF item_name LIKE 'BUC%'
THEN
    SET NEW.ratio = NEW.item_value/(
        SELECT SUM(item_value) 
        FROM Table1 
        WHERE item_name = NEW.item_name AND month = NEW.month AND year = NEW.year
    );
END IF$$

DELIMITER ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0