Easiest Way to Eliminate Nulls in Select Distinct

Easiest way to eliminate NULLs in SELECT DISTINCT?

Try this:

select distinct * from test
where b is not null or a in (
  select a from test
  group by a
  having max(b) is null)

You can get the fiddle here.

Note if you can only have one non-null value in b, this can be simplified to:

select a, max(b) from test
group by a

Does DISTINCT rule out Nulls

You Kind of have 2 questions between your title and your narrative.

DISTINCT does NOT eliminate (rule out) Nulls

However

Aggregate Functions IGNORE Null Values

As others have mentioned so if you want to count all NON NULL DISTINCT Values use the code you mentioned.

SELECT COUNT(DISTINCT columnName)

If you want to count all nulls as another value you can do that 1 of 2 ways.

1) Use COALESCE() to eliminate the null with a value that is not represented within your dataset. E.g.

 SELECT COUNT(DISTINCT COALESCE(columnName,'|||||||||'))

2) the more certain way use conditional aggregation similar to what Gordon showed:

To show how distinct does not eliminate null values:

CREATE TABLE DistinctTest (Col INT)
INSERT INTO DistinctTest (Col) VALUES (NULL),(1),(2),(3),(NULL)

SELECT DISTINCT  *
FROM
    DistinctTest

Select Distinct on one column and eliminate nulls in Select Distinct?

Using John Cappelletti's sample data here is another approach. All you really needed was to add the OR predicate to your where clause.

Declare @YourTable Table ([ID] int,[SKU] varchar(50),[PRODUCT] varchar(50))
Insert Into @YourTable Values 
 (1,'FOO-23','Orange')
,(2,'BAR-23','Orange')
,(3,'FOO-24','Apple')
,(4,'FOO-25','Orange')
,(5,'FOO-25',NULL)
,(6,'FOO-25',NULL)

SELECT  *
FROM
(
    SELECT ID
        , SKU
        , Product
        , ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
    FROM   @YourTable
    WHERE  SKU LIKE 'FOO%'
) AS a
WHERE  a.RowNumber = 1
    OR a.PRODUCT IS NULL --This was the only part you were missing

How to remove null value from distinct column of a table in SQL?

please try below query

select distinct city from billingsystem  where city is not null

Get SQL Distinct Row Without NULL Values

You seem to want aggregation:

select id, max(segmentdate1) as segmentdate1, max(converted1) as converted1,
       max(segmentdate2) as segmentdate2, max(converted2) as converted2
from t
group by id;

Note: I made up names for the columns so they are unique.

This is probably a result set created from another query. That query probably has the wrong group by keys. You should probably fix that query.

Count distinct and Null value is eliminated by an aggregate

select a,count(distinct isnull(b,-1))-sum(distinct case when b is null then 1 else 0 end),sum(a) from 
    (select 1 a,1 b union all
    select 2,2 union all
    select 2,null union all
    select 3,3 union all
    select 3,null union all
    select 3,null) a
    group by a

Thanks to Eoin I worked out a way to do this. You can count distinct the values including the nulls and then remove the count due to nulls if there were any using a sum distinct.

How to select distinct columns and return non null values

When dealing with NULL use the IS NULL or the IS NOT NULL

 SELECT DISTINCT FormTitle, FormSection 
 FROM Table1 c
 FULL OUTER JOIN Table2 l
   ON c.FormSectionID = l.FormSectionID 
 FULL OUTER JOIN Table3 f
  ON c.FormID = f.FormID
 WHERE FormTitle IS NOT NULL 
   AND FormSection IS NOT NULL

---

Related Topics