SQL Server, Remove null values in a select case query

Question

The code below generates around 300 rows, but only a small fraction of them has any value in column "Unit=3". The rest have null values, and hence many duplicate values in column "ekod" exists.

Does anyone know how to remove all rows with a null value in the column "unit=3"?

Best regards!

Result:

ekod    unit=3    
0004    NULL
0114    15
0114    NULL
0114    NULL
0120    NULL
0120    NULL
0120    46
0120    NULL

Code:

 select 
  A.ekod
    ,case when A.unit='3' then count(*) end AS [Unit=3]
  from [Stat_unitdata].[dbo].[XXX_YYY] A
  group by a.ekod, a.unit
  order by ekod

Vamsi Prabhala · Accepted Answer · 2015-11-25 15:16:19Z

4

You can use sum.

select 
A.ekod
,sum(case when a.unit='3' then 1 else 0 end) AS [Unit=3]
from [Stat_unitdata].[dbo].[XXX_YYY] A
group by a.ekod
order by ekod

edited Nov 25, 2015 at 15:16

answered Nov 25, 2015 at 15:12

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xQbert Over a year ago

Isn't it the a.unit in the group by causing the additional records? so would having A.Unit=3 help as well? The lack of displaying a.unit in the select is hiding the fact that ekod is being duplicated from different a.units I think.

xQbert Over a year ago

Darn those simpler and cleaner answers :P but I think it may fail this way... I'm not sure how the case statement would work without the group by; but it may be fine since it's nested in a sum now.

Julien Vavasseur Over a year ago

@vkp case when a.unit='3' then 1 end gives the same result since 0 or null won't be added to the total

user2995808 Over a year ago

@vkp Thanks for all your answers! It helped a lot in solving my problems!

Gordon Linoff · Accepted Answer · 2015-11-25 15:19:01Z

2

As a note, if you don't care about ekods with zero units:

select a.ekod, count(*) as [Unit=3]
from [Stat_unitdata].[dbo].[XXX_YYY] a
where a.unit = '3'
group by a.ekod
order by a.ekod;

This returns only ekod values that have at least one unit = '3'.

answered Nov 25, 2015 at 15:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

user2995808 Over a year ago

Thanks for your help! I actually, at first, needed to remove all the zero values but changed my mind later on!

Collectives™ on Stack Overflow

SQL Server, Remove null values in a select case query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related