SQL Server count number of distinct values in each column of a table

Question

I have a table with ~150 columns. I'd like to find the count(distinct(colName)) for each column but am wondering if there's a way to do so without actually typing out each column name.

Ideally I would use count(distinct(*)) but this doesn't work.

Any other suggestions?

EDIT:

if this is my table:

  id         col1         col2        col3      ...
  01         10001       west         north  
  02         10001       west         south  
  03         10002       east         south  
  04         10002       west         north  
  05         10001       east         south  
  06         10003       west         north

I'm looking for this output

count(distinct(id))   count(distinct(col1))    count(distinct(col2))   count(distinct(col3))
       6                       3                    2                      2

Wait is it possible to have a table with a single column name used more than once? Or do you have a field that stores a column name value? — Benjamin Danger Johnson, Sep 27 '12 at 16:30

score 5 · Answer 1 · answered Sep 27 '12 at 16:29

You can do this:

DECLARE @query varchar(max)
    SELECT @query = 
    'SELECT ' + SUBSTRING((SELECT ',' +'COUNT(DISTINCT(' + column_name + ')) 
             As ' + column_name + ' '  
             FROM information_schema.columns
             WHERE 
             table_name = 'table_name'
             for xml path('')),2,200000)  +  'FROM table_name'

PRINT(@query)

score 2 · Answer 2 · answered Sep 27 '12 at 16:38

Use the below script to build T-SQL query that will return a distinct count of each column in a table. Replace @Table value with your table name.

DECLARE @Table SYSNAME = 'TableName'; 

-- REVERSE and STUFF used to remove trailing UNION in string
SELECT REVERSE(STUFF(REVERSE((SELECT 'SELECT ''' + name 
                                     + ''' AS [Column], COUNT(DISTINCT(' 
                                     + QUOTENAME(name) + ')) AS [Count] FROM ' 
                                     + QUOTENAME(@Table) + ' UNION ' 
                              -- get column name from sys.columns  
                              FROM   sys.columns 
                              WHERE  object_id = Object_id(@Table)
                              -- concatenate result strings with FOR XML PATH
                              FOR XML PATH (''))), 1, 7, ';'));

Great answer! This orders the columns alphabetically, which doesn't work so well when there are lots (150) of columns. I will add an answer that builds on this one instead of editing this (for narrow tables this works well!). — J. Chris Compton, Mar 21 '18 at 15:54

J. Chris Compton · Answer 3 · 2018-03-21T18:57:40.153

Expanded answer from Bryan. His great answer lists the fields alphabetically.
This is no problem if you have a dozen fields or so. If you have 150 fields, like OP stated, this keeps the fields in their table's order.
I modified his query in order to examine a 213 column (vendor's) table and wanted to post for future reference.

DECLARE @Table SYSNAME = 'Your table name; without schema; no square brackets'; 

-- REVERSE and STUFF used to remove trailing UNION in string
SELECT REVERSE(STUFF(REVERSE((SELECT 'SELECT ' 
        + CAST(column_id AS VarChar(4)) + ' AS [column_id],' -- extra column
        + '''' + name 
        + ''' AS [Column], COUNT(DISTINCT(' 
        + QUOTENAME(name) + ')) AS [Count] FROM ' 
        + QUOTENAME(@Table) + ' UNION ' 
    -- get column name from sys.columns  
    FROM   sys.columns 
    WHERE  system_type_id NOT IN (34,240) AND object_id = Object_id(@Table) 
    ORDER BY column_id -- keeps columns in table order
    -- concatenate result strings with FOR XML PATH
    FOR XML PATH (''))), 1, 7, ';'));

I decided to not edit Bryan's answer because often people won't need the extra column.
(The ORDER BY has no effect if you don't add the column_id column. I believe this is because only the outermost ORDER BY is guarenteed to order the final output; I'd love to have a msft reference that confirms this)

EDIT: Using function Count with field types Image and Geography throws error.
Added "system_type_id NOT IN (34,240)".

I wanted to wrap it all up into a single query using sp_executesql, but didn't have time. I'll up-vote it if anyone posts that answer :–) — J. Chris Compton, Mar 21 '18 at 16:56

score 0 · Answer 4 · answered Sep 27 '12 at 16:19

I don't believe this is possible with just MySQL. I would think your going to have to use a server side language to get the results you want.

Use "DESC TABLE" as your first query and then for each "field" row, compile your query.

Ignore this, wrong system tag :)

Void Ray · Answer 5 · 2012-09-27T16:31:17.307

-1

This should do:

select count(*) from (select distinct * from myTable) as t

Here is SQL Fiddle test.

create table Data
(
    Id int,
    Data varchar(50)
)

insert into Data 
select 1, 'ABC'
union all
select 1, 'ABC'

select count(*) 
from (select distinct * from Data) as t

edited Sep 27 '12 at 16:31

answered Sep 27 '12 at 16:24

Void Ray

9,849
4
33
53

SQL Server count number of distinct values in each column of a table

5 Answers5

Linked