Drizzle TeamDT
Drizzle Team•2y ago
Jinx ᓚᘏᗢ

trying to create a trigger on supabase to calculate a winrate based on two other columns

hi all, tsuki here. i'm trying to create a trigger that fires before updates, that should calculate a win percentage based on my wins column and my losses column.

the trigger is firing correctly, but it seems like it isn't correctly calculating the win percentage?

i expected the attached image to have winrate as 60.00 or 60 given the calculation of 6 / (6 + 4) * 100.

the code used to create the trigger in the sql editor on supabase some of which was written by the supabase ai helper.
create
or replace function calculate_winrate () returns trigger as $$
begin
    new.winrate = (new.wins / (new.wins + new.losses)) * 100;
    return new;
end;
$$ language plpgsql;

create
or replace trigger calculate_winrate_trigger
before
update on "mtg-player_deck" for each row
execute function calculate_winrate ();


any comments or help is greatly appreciated :JinxHeart:
image.png
Solution
you are dividing two ints - you get an int as a result(rounded down).
6 / (6 + 4) * 100 -> 6 / 10 * 100 -> 0 * 100

multiply new.wins by 1.0 (not just 1) - should help. or do a coalesce to float on one of columns
new.winrate = ((1.0 * new.wins) / (new.wins + new.losses)) * 100;
Jump to solution
Was this page helpful?