I'm starting with Supabase and would like to understand how I can pass the SUM() aggregator in a SELECT.
I noticed that for COUNT we use:
const { data, error, range, count } = supabase .from('table') .select('*', { count: 'exact' }) Is there anything similar to SUM that I haven't noticed?
My query is this:
select "GE_PRODUCTS", sum("GE_QUANTITY") as Quantity, sum("GE_SALEVALUE") as Revenue from "GE_SELLS" where "GE_ENTERPRISE" = 'G.E.' and "DELETED" <> '*' group by "GE_PRODUCTS" order by Revenue desc limit 3; 2 Answers
Your best bet is to put this into a PostgreSQL function and call it from Supabase using .rpc():
CREATE OR REPLACE FUNCTION get_my_sums() RETURNS TABLE ( "GE_PRODUCTS" TEXT, Quantity NUMBER, Revenue NUMBER ) AS $$ DECLARE var_r record; BEGIN RETURN QUERY select "GE_PRODUCTS", sum("GE_QUANTITY") as Quantity, sum("GE_SALEVALUE") as Revenue from "GE_SELLS" where "GE_ENTERPRISE" = 'G.E.' and "DELETED" <> '*' group by "GE_PRODUCTS" order by Revenue desc limit 3; END; $$ LANGUAGE 'plpgsql'; 
