Sql-server – How to find the sum and overpayment of three table values

aggregatesql serversql-server-2008-r2

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.

Best Answer

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 > 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) > 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;

Related Solutions

PostgreSQL – Avoiding SUM Over Same Values Multiple Times

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list.
Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name.
You probably also need coalesce() for students without any scores:

SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id, st.name, st.bonus ;

In newer versions of Postgres, you could use only the primary key of the student table in the group by:

SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id ;

If you want a subquery, this is one way:

SELECT st.name, 
       coalesce(sc.score, 0) + st.bonus AS total
FROM student st
LEFT JOIN 
    ( SELECT student_id, sum(score1) + sum(score2) AS score
      FROM score 
      GROUP BY student_id
    ) AS sc ON sc.student_id = st.id ;

SQL Server – How to Sum Values from Two Tables Using Inner Join

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

Best Answer

Related Solutions

PostgreSQL – Avoiding SUM Over Same Values Multiple Times

SQL Server – How to Sum Values from Two Tables Using Inner Join

Related Question