how to get ALL amount_left with ALL partyname checking that ALL partyname same as in another table w-CodePudding

Here is the First Table:

Sales:

Restaurant Teliabagh    80000
Restaurant Teliabagh    20000
SHRI BALAJI 50000
Bhola ji    50000
Ajay    50000

Second Tables:

Cash:

Restaurant Teliabagh    20000 
Restaurant Teliabagh    20000 
Ajay    25000

I was able to get money_left for one party AJAY only amount not with his name

here is the command:

SELECT (select sum(totalamount) from acc.sales where Partyname='Ajay')-(select sum(Amountrecevied) from acc.cash where Party_name='Ajay') as money_left;

and output as

money_left

25000

BUT my problem is that Here I want to display the ALL Partyname with Money_left After subtraction (totalamount) of the partynames in sales table form (amount received) in cash tables.

IT will be fine if it displays partyname and amount as NUll after subtraction them.

CodePudding user response：

SELECT Partyname, sales.totalamount - COALESCE(cash.totalamount, 0) as money_left
FROM ( SELECT Partyname, SUM(totalamount) totalamount
       FROM acc.sales
       GROUP BY 1 ) sales
LEFT JOIN ( SELECT Partyname, SUM(totalamount) totalamount
            FROM acc.cash
            GROUP BY 1 ) cash USING (Partyname);

If there exists Partyname value which is present in cash but is absent in sales then additional subquery with UNION DISTINCT which gathers all Partyname values needed as a base.