CODEWITHSUNDEEP
sqlt-sql
greener
greener

Reputation: 5069

Displaying occurrences of NULL values and overall duplicates with SQL

With data such as the below, I need to generate a report that reports back the number of records with NULL and the number of duplicates, all with one SQL query if possible.

DES   |  VAL
--------------
Tango |  32
Zulu  |  [null]
Golf  |  12
Golf  |  12
Bravo |  [null]

The report would look like:

NULLS  |  DUPLICATES
---------------------
  2    |    1

I can get the nulls with something like SUM(CASE VAL WHEN NULL THEN 1 ELSE 0 END) AS NULLS, and duplicates separately, but not as one query so I don't even know if it's possible.

Upvotes: 0

Views: 249

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Not sure how you want to count your duplicates so I included two versions.

declare @T table
(
  DES varchar(10),
  VAL int
)

insert into @T values
('Tango',   32),
('Zulu',    null),
('Zulu',    null),
('Zulu',    null),
('Golf',    12),
('Golf',    12),
('Bravo',   null)

select sum(case when T.VAL is null then C end) as NULLS,
       sum(case when T.C > 1 then C-1 end) as DUPLICATES1,
       sum(case when T.C > 1 then 1 end) as DUPLICATES2
from (
       select VAL, count(*) as C
       from @T
       group by DES, VAL
     ) T

Result:

NULLS       DUPLICATES1 DUPLICATES2
----------- ----------- -----------
4           3           2

Upvotes: 0

HABO
HABO

Reputation: 15816

Assuming (?!) that you want to count duplicate rows, this may come close to what you want:

declare @Foo as Table ( DES VarChar(10), VAL Int Null )
insert into @Foo ( DES, VAL ) values
  ( 'Tango', 32 ),
  ( 'Zulu', NULL ),
  ( 'Golf', 12 ), ( 'Golf', 12 ), ( 'Golf', 13 ),
  ( 'Bravo', NULL ),
  ( 'Whiskey', 8388 ), ( 'Whiskey', 8388 ), ( 'Whiskey', 8388 ), ( 'Whiskey', 8388 )

select * from @Foo

select distinct DES, VAL from @Foo

select ( select Count( 42 ) from @Foo where VAL is NULL ) as [NULLS],
  ( select Count( 42 ) from @Foo ) - Count( 42 ) as [DUPLICATES] from ( select distinct DES, VAL from @Foo ) as Elmer

Upvotes: 0

Pavel Strakhov
Pavel Strakhov

Reputation: 40502

SELECT
  (SELECT COUNT(*) FROM table_name WHERE val IS NULL) 
    AS NULLS,
  (SELECT ( COUNT(val) - COUNT(DISTINCT(val)) ) FROM table_name) 
    AS DUPLICATES

Upvotes: 1

Jesus is Lord
Jesus is Lord

Reputation: 15399

Well if you have 2 selects returning scalar values that you want to combine into a simple report like that, you could do:

SELECT 
2 AS NULLS,
DUPS
FROM (SELECT 1 AS DUPS) D

Results:

NULLS       DUPS
----------- -----------
2           1

Replacing the two selects as needed.

Upvotes: 0

Related Questions