CODEWITHSUNDEEP
sqlsql-server
user2995808
user2995808

Reputation: 51

SQL Server, Remove null values in a select case query

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

Upvotes: 0

Views: 3354

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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'.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 4

Related Questions