CODEWITHSUNDEEP
sqlsql-server
Hackmodford
Hackmodford

Reputation: 3970

MS SQL: Table Calculation for each Result

I want to calculate the "balance" for a customer in my db.

Customer Table
-----------------
1 - Frank
2 - Bob

Invoice Table
---------------------------------------------------
id | customer_id | amount_due | credit_due
---------------------------------------------------
1  | 1           | $10        | 
---------------------------------------------------
2  | 1           |            | $6
---------------------------------------------------

Here's a typical sql statement for looking up a customers info.

SELECT id, name FROM CUSTOMER WHERE id = ?

If I want to also retrieve the balance, I'm doing this:

SELECT a.*, b.* FROM 
(SELECT id, name FROM CUSTOMER WHERE id = ?) AS a, 
(SELECT SUM(amount_due - credit_due) AS balance FROM INVOICE WHERE cus_id = ?) AS b

Running this result:

--------------------
id | name | balance 
---------------------
1 | Frank | $4

This works great when retrieving just one customer, but what if I want to do a more general search? Like loading ALL customers?

This is the output I would like to get

--------------------
id | name | balance 
---------------------
1 | Frank | $4
---------------------
2 | Bob   | $0

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You would just use aggregation, I think:

select c.customer_id, c.name,
       ( coalesce(sum(i.amount_due), 0) - coalesce(sum(i.credit_due), 0) ) as balance
from customer c join
     invoice i
     on i.customer_id = c.customer_id
group by c.customer_id, c.name;

I should note that if the amount fields are NULL, then the expression sum(amount_due - credit_due) is not doing what you expect.

Upvotes: 2

Related Questions