0

TABLE

Col1 Col2 Col3
a    1    Z
a    2    Z
a    null empty
b    1    Z
b    2    X
b    3    Y
b    3    Y

I want to know the count of distinct values by column.

  • Col1 count = 2
  • Col2 count = 3 (or 4 uncertain null handling)
  • Col3 count = 3 (or 4 uncertain empty handling)

I have been attempting different counting and grouping clauses, but this may be not the way.

Dale K
  • 25,246
  • 15
  • 42
  • 71
PaulM
  • 345
  • 2
  • 11
  • 4
    This would look like `SELECT COUNT(DISTINCT col1), COUNT(DISTINCT col2), COUNT(DISTINCT col3) FROM yourtable` – JNevill Aug 03 '23 at 20:27
  • This is not a duplicate. Appreciate the syntax given in comment and answer. – PaulM Aug 03 '23 at 22:07
  • 1
    What about it is not a duplicate? The accepted answer is the same as the duplicate answer? – Dale K Aug 03 '23 at 22:33

1 Answers1

3

count(distinct ...) should do the trick

Example

Declare @YourTable Table ([Col1] varchar(50),[Col2] int,[Col3] varchar(50))  Insert Into @YourTable Values 
 ('a',1,'Z')
,('a',2,'Z')
,('a',null,'')  -- Note the empty string
,('b',1,'Z')
,('b',2,'X')
,('b',3,'Y')
,('b',3,'Y')
 
Select Col1 = count(distinct col1)
      ,Col2 = count(distinct col2)
      ,Col3 = count(distinct nullif(col3,''))  -- Remove NullIf() if you want to count empty strings
 From  @YourTable

Results

Col1  Col2  Col3
2     3     3
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • `count(distinct nullif(col3,''))` is going to be a little inefficient as it cannot use indexes. Might be better to do `count(distinct col3) - iif(count(case when col3 = '' then 1 end) > 0, -1, 0)` – Charlieface Aug 03 '23 at 20:59
  • @Charlieface. Just illustrating a method to exclude empty strings. Personally, an empty string is a value. I also I would have to benchmark your suggestions to be convinced – John Cappelletti Aug 03 '23 at 21:20