CODEWITHSUNDEEP
sql
IElite
IElite

Reputation: 1848

Filtering my SQL to show only those records > 0

I have two tables 

Master   
Detail

In the Master & Detail table

I get the SUM of Amount Applied in Detail and subtract it from Initial Amount in Master and display in "REMAIN"

select m.ID, m.Init_Amnt, m.Cur_Amnt, max(m.Init_Amnt) - sum(d.Amnt_appl) REMAIN   
from AMASTER m   
left join ADETAIL D on m.ID = d.Master_ID   
group by m.ID, m.Init_Amnt, m.Cur_Amnt

Is there a way to show only those where the remain in not null OR remain > 0.00

Upvotes: 0

Views: 86

Answers (3)

David M
David M

Reputation: 72900

Because this is a filter on an aggregated result, you need a HAVING clause. Depending on your flavour of SQL, something like this:

HAVING ISNULL(max(m.Init_Amnt) - sum(d.Amnt-Appl), 1) > 0

In this, 1 is just a "magic number" to meet the > 0 condition in the case of a null.

Upvotes: 4

Eric
Eric

Reputation: 8088

Try this:

select m.ID,m.Init_Amnt, m.Cur_Amnt, max(m.Init_Amnt) - sum(d.Amnt_appl) REMAIN    
from AMASTER m    
left join ADETAIL D on m.ID = d.Master_ID    
group by m.ID, m.Init_Amnt, m.Cur_Amnt   
HAVING ISnull(max(m.Init_Amnt),0) - IsNull(sum(d.Amnt_appl),0) > 0.0

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270697

A HAVING clause is what is needed:

select
   m.ID, 
   m.Init_Amnt,
   m.Cur_Amnt,
   max(m.Init_Amnt) - sum(d.Amnt_appl) REMAIN   
from AMASTER m   
left join ADETAIL D on m.ID = d.Master_ID   
group by m.ID, m.Init_Amnt, m.Cur_Amnt  
HAVING REMAIN > 0.0

Note some RDBMS won't allow the syntax above (it works for MySQL), using the column alias. Instead you may need to place the full calculation into HAVING (SQL Server acts this way, as does Oracle)

select
   m.ID, 
   m.Init_Amnt,
   m.Cur_Amnt,
   max(m.Init_Amnt) - sum(d.Amnt_appl) REMAIN   
from AMASTER m   
left join ADETAIL D on m.ID = d.Master_ID   
group by m.ID, m.Init_Amnt, m.Cur_Amnt  
HAVING max(m.Init_Amnt) - sum(d.Amnt_appl) > 0.0

Upvotes: 1

Related Questions