SQL query to generate table ordered by Balance

I'm working with SQL server and ASP...

I have 3 tables:
-Clients (contains CLIENT_ID)
-Invoices (contains INVOICE_ID and INVOICE_AMOUNT fields, and is related to Clients with CLIENT_ID field)
-Payments (contains PAYMENT_ID and PAYMENT_AMOUNT fields, and is related to Invoices with INVOICE_ID field)

I'm trying to build a query to generate a table with the next columns...
Client_ID     Total_Invoices_Amount       Total_Payments_Amount      Balance
----------      ---------------------------      -----------------------------      ---------

Table must be grouped by Client_ID.
Total_Invoices_Amount field = sums all amounts of invoices made for each client.
Total_Payments_Amount = sums all amounts of payments made by each client.
Balance = Difference of Total invoices and Total payments (Total_Invoices_Amount - Total_Payments_Amount).
Table must be ORDERED BY BALANCE FIELD desc.

This must be all contained into the same query to make possible ORDER BY Balance.
I have traied a lot of queries w/o success. Just wrong calculations.
egwertaAsked:
Who is Participating?
 
xxg4813Connect With a Mentor Commented:
Try this one, it should work

---------------------------------------------------------------------------------------

select c.client_id,
       isnull(i.invoice_amount,0) Total_Invoices_Amount,
       isnull(p.payment_aount,0) Total_Payments_Amount,
       Total_Invoices_Amount - Total_Payments_Amount Balance
from clients c
left outer join (select client_id, sum(invoice_amount) invoice_amount
                 from invoices
                 group by invoice_id) i on c.client_id = i.client_id
left outer join (select c.client_id, sum(p.payment_amount) payment_amount
                 from clients c, invoices i, payments p
                 where c.client_id = i.client_id
                   and i.invoice_id = p.invoice_id
                 group by c.client_id) p on c.client_id = p.client_id

-------------------------------------------------------------------------------------------
0
 
SQL_StuCommented:
Try:

Select a.client_ID, sum(b.INVOICE_AMOUNT), sum(c.PAYMENT_AMOUNT), sum(c.PAYMENT_AMOUNT)-sum(b.INVOICE_AMOUNT) as Balance
from clients a, invoices b, payments c
where a.client_ID = b.CLIENT_ID
and b.INVOICE_ID = c.INVOICE_ID
group by a.Client_ID
order by sum(c.PAYMENT_AMOUNT)-sum(b.INVOICE_AMOUNT)
0
 
jdlambert1Commented:
Try this:

SELECT c.Client_ID, Total_Invoice_Amount, Total_Payment_Amount,
 Total_Invoice_Amount - Total_Payment_Amount as Balance
FROM (--get invoice totals
   SELECT c.Client_ID, Sum(i.Invoice_Amount) Total_Invoice_Amount
   FROM Clients c INNER JOIN Invoices i ON c.Client_ID = i.Client_ID
   GROUP BY Client_ID
) i INNER JOIN (--get payment totals
   SELECT c.Client_ID, Sum(p.Payment_Amount) Total_Payment_Amount
   FROM Clients c INNER JOIN Payments p ON c.Client_ID = p.Client_ID
   GROUP BY c.Client_ID
) p ON i.Client_ID = p.Client_ID
ORDER BY 4 DESC
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
peh803Commented:
Okay, don't laugh --- here goes nothing...

SELECT
  C.Client_ID,
  (
    SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID
  ) AS Total_Invoices_Amount,
  (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID)
  ) AS Total_Payment_Amount,
  (
    (SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID) -
    (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID))
  ) AS Balance
FROM Clients
ORDER BY
  ((SELECT SUM (I.Invoice_Amount) FROM Invoices I WHERE I.Client_ID = C.Client_ID) -
    (SELECT SUM (P.Payment_Amount) FROM Payments P WHERE Invoice_ID IN
      (SELECT Invoice_ID FROM Invoices In WHERE In.Client_ID=C.Client_ID)))

peh803
0
 
HilaireCommented:
Can you give it a try ?

Select a.Client_ID,
SUM(a.INVOICE_AMOUNT) as Total_Invoices_Amount,
SUM(b.PAYMENT_AMOUNT ) as Total_Payments_Amount,
SUM(a.INVOICE_AMOUNT) - COALESCE(SUM(b.PAYMENT_AMOUNT ), 0) as Balance
From Invoices a LEFT OUTER  JOIN Payments b on a.INVOICE_ID = b.INVOICE_ID
ORDERED BY 4 desc

Cheers

Hilaire
0
 
HilaireCommented:
Oops, missing group by + Typo
The left outer join is important to keep the invoices when no payment avail

Select a.Client_ID,
SUM(a.INVOICE_AMOUNT) as Total_Invoices_Amount,
SUM(b.PAYMENT_AMOUNT ) as Total_Payments_Amount,
SUM(a.INVOICE_AMOUNT) - COALESCE(SUM(b.PAYMENT_AMOUNT ), 0) as Balance
From Invoices a LEFT OUTER  JOIN Payments b on a.INVOICE_ID = b.INVOICE_ID
GROUP BY a.Client_ID
ORDER BY 4 desc
0
 
xxg4813Commented:

forgot the order by balance desc

u can do it by yourself

good luck!
0
 
peh803Commented:
@egwerta -- is this still an issue for you?  Please let us know so we can help out (if you still need it).  Otherwise, please post any relevant comments and close the question.

Thank you,
peh803
0
 
egwertaAuthor Commented:
This was the final working sentence (in spanish)...

SELECT A.Client_ID, B.Sum_Invoices, C.Sum_Payments, (isnull(B.Sum_Invoices,0) - isnull(C.Sum_Payments,0)) AS Balance
FROM (SELECT DISTINCT Client_ID FROM Clients) A
INNER JOIN (SELECT Client_ID, SUM(B2.Unit_Price * B2.Qty) AS Sum_Invoices
FROM Invoices B1 INNER JOIN Invoice_Details B2 ON B1.Invoice_ID = B2.Invoice_ID
GROUP BY Client_ID) B
ON A.Client_ID = B.Client_ID
LEFT JOIN (SELECT Client_ID, SUM(Total_Payment) AS Sum_Payments
FROM Payments GROUP BY Client_ID) C ON A.Client_ID = C.Client_ID
ORDER BY Balance DESC
0
 
peh803Commented:
Thanks for updating / closing this question -- glad you were able to get a working solution in place.

peh803
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.