Geek Answers Handbook

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

I have this query:

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

But there is a problem with this; there are other TenantIDs that have no transactions, and I also want to receive them.

For example, a transaction table has 3 rows for bob, 2 rows for john and none for jane. I want him to return the amount for bob and john And return 0 for jane. (or possibly null if there is no other way)

How can i do this?

The tables are as follows:

  Tenants  
   ID  
   Other data  
 Transactions  
   ID  
   TenantID (fk to Tenants)
   Other data
+4
source share
5 answers

(You did not specify your SQL engine, so I'm going to refer to the MySQL documentation).

This pretty much corresponds to the COALESCE() function. You can give it a list and it will return the first nonzero 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;

Thus, if the result of SUM() is NULL, it will be replaced by zero.

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

+14
source

Below is a complete overview of the problem. The isnull function is also included to provide zero (rather than zero) balance returns for non-transactional tenants.

 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;
+1
source 
 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 did not check the syntax, but it is close enough.

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

I just added this because if you intend to take into account that for one of the parts that are null, you will need to do ISNULL separately

0
source

Actually, I found the 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
-1
source

More articles:


All Articles