SQLTeam.com | Weblogs | Forums

Removing null from Result set [Solved]


#1

Hi all,
Please help me with this code, I want to remove the nulls from the result set so the result is 1 line. Code and results are below:

SELECT        
CustomerNumber,
(case when yearseq = 2012 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2012',
(case when yearseq = 2013 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2013',
(case when yearseq = 2014 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2014',
(case when yearseq = 2015 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2015'
FROM ProductPower
Where customernumber in ('1107937') and yearseq is not null
group by CustomerNumber,yearseq
order by customernumber

Results look like this:
[Result picture Link][1]
[1]: https://gyazo.com/583d071b7b020f8378ccef9a4fb5901f


#2

Something like this.

SELECT        
CustomerNumber,
SUM(CASE WHEN yearseq = 2012 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0)  ELSE 0 END ) AS [2012],    
SUM(CASE WHEN yearseq = 2013 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0) ELSE 0 END ) AS [2013],    
SUM(CASE WHEN yearseq = 2014 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0)  ELSE 0 END ) AS [2014],    
SUM(CASE WHEN yearseq = 2015 THEN isnull(mainPower,0)+isnull(sidePower,0)+isnull(leftPower,0)+isnull(netappPower,0)+isnull(rightPower,0)+isnull(lowerPower,0)  ELSE 0 END ) AS [2015]    
FROM ProductPower
Where customernumber in ('1107937') and yearseq is not null
group by CustomerNumber
order by customernumber

#3

Geez I was kinda close, but thank you so much.


#4

I didn't notice.