Minus values Oracle SQL

I have the below Oracle working fine however I want to calculate Sum(manhours)-Leavetaken

I am unsure if this is because some of values of Leavetaken are null so it's pulling back null if I try it as above. But if even leavetaken is null, I want just the full value of sum(manhours)

Here's my current query:

select lsc.work_date, location,site, sum(manhours), Leavetaken from location_site_capacity lsc left outer join ( select work_date, sum(manhours) as Leavetaken from location_site_capacity where break is not null group by work_date) A on A.work_date = lsc.work_Date where lsc.work_date between '01/JUN/15' and '30/JUN/15' group by lsc.work_date, location,site, Leavetaken order by lsc.work_Date,location

--------------Solutions-------------

Use coalesce() on Leavetaken, like this:

select ..., sum(manhours) - coalesce(Leavetaken, 0) as calc

coalesce() returns the first non-null value.

Category:oracle Time:2018-07-05 Views:0
Tags: oracle

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.966 (s). 14 q(s)