https://supabase.com/ logo
#sql
Title
# sql
m

Muezz

04/26/2022, 8:12 PM
Is there a built-in way/function to achieve something like this? Going from Table 1 and 2 to Table 3 (i.e. 1-2=3)
t

tourdownunder

04/26/2022, 8:20 PM
Along with coalesce nulls to 0 as null - 15 will be Null IIRC
m

Muezz

04/26/2022, 8:21 PM
I see that I am on the right track. I am trying to do this but it is not working
Copy code
sql
SELECT
    db_transactions.deb_acc,
    coalesce(sum(db_transactions.amount),0) as "debit_total"
FROM
    public.db_transactions
GROUP BY
    db_transactions.deb_acc,
FULL JOIN 
    (SELECT
        db_transactions.cred_acc,
        coalesce(sum(db_transactions.amount),0) as "credit_total"
    FROM
        public.db_transactions)
    GROUP BY
        db_transactions.cred_acc;)
ON 
    db_transactions.deb_acc=db_transactions.cred_acc;
Both the
deb_acc
and the
cred_acc
are in the same table
t

tourdownunder

04/26/2022, 8:23 PM
Yeah right track.
m

Muezz

04/26/2022, 8:23 PM
Can you have a look at this piece of sql code and let me know if I am making a mistake?
If I run both SELECT statements without the JOIN, I get the total debits and credits as expected
t

tourdownunder

04/26/2022, 8:26 PM
Unlikely able to help without a minimum reproducible example example including creating tables inserting fake data etc
Though I will try
You say your query is not working. Why? What error?
m

Muezz

04/26/2022, 8:27 PM
I made my table manually so if you give me a couple mins, I can write up the sql to recreate it. Or i could send a screenshot of the schema? Whatever helps you the most.
I get this
syntax error at or near "JOIN"
t

tourdownunder

04/26/2022, 8:28 PM
Code. I need to be able to run it!
m

Muezz

04/26/2022, 8:28 PM
okay. gimme a few mins, I'll be back.
t

tourdownunder

04/26/2022, 8:30 PM
Ohh you have a semi colon in a sub query# That’s not good
m

Muezz

04/26/2022, 8:32 PM
I am still new to sql syntax. I removed it and I am still getting the same error.
t

tourdownunder

04/26/2022, 8:38 PM
Continue with the minimum reproducible example and I’ll take a look after coffee.
m

Muezz

04/26/2022, 8:47 PM
Copy code
sql
-- Creates the table with the least number of columns required
create table test_table (
  id uuid DEFAULT uuid_generate_v4 ()  primary key,
  deb_acc text NOT NULL,
  cred_acc text NOT NULL,
  amount numeric NOT NULL
);
-- Inserts random data
INSERT INTO test_table(deb_acc, cred_acc,amount)
VALUES ('acc_1', 'acc_2',5),
    ('acc_3', 'acc_4',10),
    ('acc_5', 'acc_2',15),
    ('acc_1', 'acc_3',20),
    ('acc_4', 'acc_1',25);

-- Fetches the total debits for each account
SELECT
    test_table.deb_acc,
    coalesce(sum(test_table.amount),0) as "debit_total"
FROM
    public.test_table
GROUP BY
    test_table.deb_acc;

-- -- Fetches the total credits for each account
-- SELECT
--     test_table.cred_acc,
--     coalesce(sum(test_table.amount),0) as "credit_total"
-- FROM
--     public.test_table
-- GROUP BY
--     test_table.cred_acc;

-- -- Should fetch the current balance of each account
-- SELECT
--     test_table.deb_acc,
--     coalesce(sum(test_table.amount),0) as "debit_total"
-- FROM
--     public.test_table
-- GROUP BY
--     test_table.deb_acc,
-- FULL JOIN 
--     (SELECT
--         test_table.cred_acc,
--         coalesce(sum(test_table.amount),0) as "credit_total"
--     FROM
--         public.test_table)
--     GROUP BY
--         test_table.cred_acc)
-- ON 
--     test_table.deb_acc=test_table.cred_acc;
I have included both SELECT statements that need to be subtracted to get the current account balance. You can uncomment and go ahead accordingly.
t

tourdownunder

04/26/2022, 9:12 PM
I think I got it
Copy code
sql
SELECT
     debits.acc,  --- its the same doesn't matter choose one
     SUM(credits.total - debits.total)
 FROM
     (SELECT
         test_table.deb_acc acc,
         coalesce(sum(test_table.amount),0) as "total"
     FROM
         public.test_table
     group by 1) as debits
 FULL JOIN 
     (SELECT
         test_table.cred_acc acc,
         coalesce(sum(test_table.amount),0) as "total"
     FROM
         public.test_table
     group by 1) as credits
 ON 
     debits.acc=credits.acc
group by 1
your example you tried to join after a group by never going to work.
Though I took inspiration on how you did the credits and replicated that in the debits and now they are like for like
m

Muezz

04/26/2022, 9:15 PM
Lemme try this...
t

tourdownunder

04/26/2022, 9:17 PM
just made one change
Copy code
sql

 SELECT
     coalesce(debits.acc, credits.acc),
     SUM(coalesce(credits.total, 0) - coalesce(debits.total, 0))
 FROM
     (SELECT
         test_table.deb_acc acc,
         coalesce(sum(test_table.amount),0) as "total"
     FROM
         public.test_table
     group by 1) as debits
 FULL JOIN 
     (SELECT
         test_table.cred_acc acc,
         coalesce(sum(test_table.amount),0) as "total"
     FROM
         public.test_table
     group by 1) as credits
 ON 
     debits.acc=credits.acc
group by 1
m

Muezz

04/26/2022, 9:22 PM
It is working, I dont know how but I am still trying to understand it. Another question, would this still be a good apprach if I end up having tens of thousands of rows eventually?
t

tourdownunder

04/26/2022, 9:24 PM
10's of thousands not a problem with group by
Nice work I got to go now.
m

Muezz

04/26/2022, 9:26 PM
Thanks a lot for the help.