How to find the sum and overpayment of three table values I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables.



Retrieve query answer should like below image from the above 3 tables.
Probably easiest to do with sub-selects:

select clientno
     , coalesce(totalamount,0) as totalamount
     , coalesce(totalpaid,0) as totalpaid
     , least(coalesce(totalamount,0)-coalesce(totalpaid,0),0) as overpaid 
from (
    select c.clientno
        , (select sum(i.totalamount) 
           from invoice i 
           where c.clientno = i.clientno) as totalamount
        , (select sum(p.totalpaid) 
           from payment p 
           where c.clientno = p.clientno) as totalpaid 
    from client c
)


If least is not availible in your DBMS something like:

case when totalamount-totalpaid &gt; 0 
     then 0 
     else totalamount-totalpaid 
end as overpaid


should do

A slightly different approach is to use LATERAL. I think this is called CROSS APPLY in sql-server. I cannot verify this at the moment so you will probably have to modify this:

select clientno
    , coalesce(totalamount,0) as totalamount
    , coalesce(totalpaid,0) as totalpaid
    , case when coalesce(totalamount,0)-coalesce(totalpaid,0) &gt; 0
           then 0 
           else coalesce(totalamount,0)-coalesce(totalpaid,0)
      end as overpaid 
from (
    select c.clientno, i.totalamount, p.totalpaid 
    from client c 
    outer apply ( 
        select sum(totalamount) as totalamount 
        from invoice 
        where clientno = c.clientno 
    ) as i 
    outer apply ( 
        select sum(totalpaid) as totalpaid 
        from payment 
        where clientno = c.clientno 
    ) as p
) as x;