Displaying occurrences of NULL values and overall duplicates with SQL

Question

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.

score 1 · Answer 1 · answered May 23 '12 at 23:18

1

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

answered May 23 '12 at 23:18

Pavel Strakhov

39,123
5
88
127

Thanks! The nulls part works great but I don't completely understand how the duplicates part works: is it only looking for duplicates in the 'val' column? – greener May 23 '12 at 23:26
Yes. `Distinct` on several columns is more complicated (see [this question](http://stackoverflow.com/questions/1471250/counting-distinct-over-multiple-columns)). I suggest to use `COUNT(DISTINCT(CONCAT(des,val)))` for this. – Pavel Strakhov May 23 '12 at 23:34
`CONCAT` is not recognized but I'm using `+` instead. I'm trying to solve the fact the data types are not the same by using `CAST` but it's not working: `COUNT(DISTINCT(des+CAST(val As varchar)))` – greener May 24 '12 at 01:49

score 0 · Answer 2 · answered May 23 '12 at 23:17

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.

score 0 · Answer 3 · answered May 24 '12 at 02:24

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

Mikael Eriksson · Accepted Answer · 2012-05-24T04:24:47.677

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

Displaying occurrences of NULL values and overall duplicates with SQL

4 Answers4