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)

A full outer join as described https://www.postgresql.org/docs/current/queries-table-expressions.html#FULL%20OUTER%20JOIN should work.

I see that I am on the right track. I am trying to do this but it is not working

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;

Yeah right track.

Can you have a look at this piece of sql code and let me know if I am making a mistake?

Unlikely able to help without a minimum reproducible example example including creating tables inserting fake data etc

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.

Code. I need to be able to run it!

okay. gimme a few mins, I'll be back.

Ohh you have a semi colon in a sub query# That’s not good

I am still new to sql syntax. I removed it and I am still getting the same error.

Continue with the minimum reproducible example and I’ll take a look after coffee.

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.

I think I got it

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

Lemme try this...

just made one change

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?

10's of thousands not a problem with group by

Thanks a lot for the help.