Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I get this query 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'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  
like image 458
Malfist Avatar asked May 19 '09 17:05

Malfist


People also ask

How do you get 0 instead of null?

UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.

How do I return 0 if null in SQL?

We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows. IFNULL(YOUREXPRESSION,0);

How do you replace null values in SQL?

We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL.


2 Answers

(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.

like image 153
Chad Birch Avatar answered Oct 18 '22 23:10

Chad Birch


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;
like image 28
John Sansom Avatar answered Oct 18 '22 23:10

John Sansom