March 13, 2014 at 8:45 am
Hi,
I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.
select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]
Any suggestions?
March 13, 2014 at 8:51 am
You can use NULLIF and COALESCE to accomplish this. I would also use LTRIM and RTRIM to get rid of extra spaces i.e. coalesce(nullif(rtrim(ltrim(column)),''),0)
March 13, 2014 at 9:01 am
Can you show an example. I am bit confused how to apply this into my query.
March 13, 2014 at 9:10 am
Coalesce will return the first non-NULL value, so if the sum Previous_Year + Current_Year returns a NULL value it will go to the next value in the comma delimited list in this case 0.
[New Value] = coalesce(SUM([Previous_Year]+[Current_Year]),0)
If you want to return a NULL instead of a certain value, i.e. empty string, you can use NULLIF. Example: nullif(ltrim(rtrim([Original])),'') AS [Originals],
Does that help? If you need more help please read the article in my signature on how to post questions on the forum and please provide the create table statement along with some sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply