CODEWITHSUNDEEP
sqlsql-server
user3665806
user3665806

Reputation: 29

SQL display duplicate field but not nulled field

I have a simple problem i think. I have in sql Server one table with this :

Name :       Sum :     CNP

Andrey       100       120
Marius       20        100
George       20        200
Popescu      Nulled    300
Antal        Nulled    100

I use this comand to show duplicate :

SELECT SUM, Name,CNP
FROM dbo.database
where SUM IN ( Select SUM from dbo.asigpag group by SUM HAVING Count(*)> 1)

Everything work ok.

In this Case show : 

Name :       Sum :     CNP

Marius       20        100
George       20        200
Popescu      Nulled    300
Antal        Nulled    100

This is the problem . I want to display duplicate but with not Nulled.

I want to display this with all the other field not only Sum.

Name :       Sum :     CNP

Marius       20        100
George       20        200

Upvotes: 0

Views: 105

Answers (2)

KoViMa
KoViMa

Reputation: 382

In SQL NULL <> NULL always. You can use IS NULL or IS NOT NULL or predefined function ISNULL(SUM, 0) - with last statement you will prepare NULL values to default 0 value. For example: 

SELECT SUM, Name,CNP
FROM dbo.database
where ISNULL(SUM, 0) IN ( Select ISNULL(SUM, 0) from dbo.asigpag group by SUM HAVING Count(*) > 1)

Update:

Sorry, I misunderstood what you want. In order to eliminate NULL from the list you need to update sub-query as:

Select SUM from dbo.asigpag where SUM IS NOT NULL group by SUM HAVING Count(*) > 1

Whole query will be:

SELECT SUM, Name, CNP
FROM dbo.database
where SUM IN ( Select SUM from dbo.asigpag Where SUM IS NOT NULL group by SUM HAVING Count(*) > 1)

Upvotes: 0

Bob Tway
Bob Tway

Reputation: 9603

You need to add another condition to exclude records that have a null value in the sum field:

SELECT SUM, Name,CNP
FROM dbo.database
where SUM IN ( Select SUM from dbo.asigpag group by SUM HAVING Count(*)> 1) 
AND SUM is not NULL

SQL Server treats NULLS differently from values, because they have no value at all. They're special case that need to be selected using [Field] IS NULL or [Field] = NULL, or their reverse, as in this case.

Upvotes: 1

Related Questions