How can I get this query to return 0 instead of null?

Question

I have this query:

SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID
 FROM tblTransaction
 GROUP BY tblTransaction.TenantID

But there's a problem with it; there are other TenantID's that don't have transactions and I want to get those too.

For example, the transaction table has 3 rows for bob, 2 row for john and none for jane. I want it to return the sum for bob and john AND return 0 for jane. (or possibly null if there's no other way)

How can I do this?

Tables are like this:

Tenants  
  ID  
  Other Data  
Transactions  
  ID  
  TenantID (fk to Tenants)
  Other Data  

Answer 1

(You didn't state your sql engine, so I'm going to link to the MySQL documentation).

This is pretty much exactly what the COALESCE() function is meant for. You can feed it a list, and it'll return the first non-null value in the list. You would use this in your query as follows:

SELECT COALESCE((SUM(tr.AmountPaid) - SUM(tr.AmountCharged)), 0) AS TenantBalance, te.ID
FROM tblTenant AS te
    LEFT JOIN tblTransaction AS tr ON (tr.TenantID = te.ID)
GROUP BY te.ID;

That way, if the SUM() result would be NULL, it's replaced with zero.

Edited: I rewrote the query using a LEFT JOIN as well as the COALESCE(), I think this is the key of what you were missing originally. If you only select from the Transactions table, there is no way to get information about things not in the table. However, by using a left join from the Tenants table, you should get a row for every existing tenant.

Answer 2

Select Tenants.ID, ISNULL((SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)), 0) AS TenantBalance
From Tenants 
Left Outer Join Transactions Tenants.ID = Transactions.TenantID
Group By Tenents.ID

I didn't syntax check it but it is close enough.

Answer 3

Actually, I found an answer:

SELECT tenant.ID, ISNULL(SUM(trans.AmountPaid) - SUM(trans.AmountCharged),0) AS Balance FROM tblTenant tenant
LEFT JOIN tblTransaction trans
ON tenant.ID = trans.TenantID
GROUP BY tenant.ID

Answer 4

SELECT (SUM(ISNULL(tblTransaction.AmountPaid, 0)) 
        - SUM(ISNULL(tblTransaction.AmountCharged, 0))) AS TenantBalance
       , tblTransaction.TenantID
        FROM tblTransaction
        GROUP BY tblTransaction.TenantID

I only added this because if you're intention is to take into account for one of the parts being null you'll need to do the ISNULL separately

Answer 5

Below is a full walkthrough of the problem. The function isnull has also been included to ensure that a balance of zero (rather than null) is returned for Tenants with no transactions.

create table tblTenant
(
    ID int identity(1,1) primary key not null,
    Name varchar(100)
);

create table tblTransaction
(
    ID int identity(1,1) primary key not null,
    tblTenantID int,
    AmountPaid money,
    AmountCharged money
);

insert into tblTenant(Name)
select 'bob' union all select 'Jane' union all select 'john';

insert into tblTransaction(tblTenantID,AmountPaid, AmountCharged)
select 1,5.00,10.00
union all
select 1,10.00,10.00
union all
select 1,10.00,10.00
union all
select 2,10.00,15.00
union all 
select 2,15.00,15.00


select * from tblTenant
select * from tblTransaction

SELECT 
    tenant.ID, 
    tenant.Name,
    isnull(SUM(Trans.AmountPaid) - SUM(Trans.AmountCharged),0) AS Balance 
FROM tblTenant tenant
    LEFT JOIN tblTransaction Trans ON 
     tenant.ID = Trans.tblTenantID
GROUP BY tenant.ID, tenant.Name;

drop table tblTenant;
drop table tblTransaction;