SQL: count number of distinct values in every column

Question

I need a query that will return a table where each column is the count of distinct values in the columns of another table.

I know how to count the distinct values in one column:

select count(distinct columnA) from table1;

I suppose that I could just make this a really long select clause:

select count(distinct columnA), count(distinct columnB), ... from table1;

but that isn't very elegant and it's hardcoded. I'd prefer something more flexible.

This is the elegant and simplest solution... do you mean "given a table name, give me distinct counts for each column in that table"? — gbn, Sep 01 '09 at 17:09
Possible duplicate: http://stackoverflow.com/questions/1330692/distinct-pair-of-values-sql — Will Bickford, Sep 01 '09 at 17:12
gbn, your restatement of my question is right on. Raj, I'm using MySQL. — Ryan, Sep 01 '09 at 19:13

score 5 · Answer 1 · edited Jun 26 '14 at 15:08

This code should give you all the columns in 'table1' with the respective distinct value count for each one as data.

DECLARE @TableName VarChar (Max) = 'table1'
DECLARE @SqlString VarChar (Max)

set @SqlString = (
  SELECT DISTINCT
    'SELECT ' + 
        RIGHT (ColumnList, LEN (ColumnList)-1) + 
      ' FROM ' + Table_Name
    FROM INFORMATION_SCHEMA.COLUMNS COL1
      CROSS AppLy (
        SELECT ', COUNT (DISTINCT [' + COLUMN_NAME + ']) AS ' + '''' + COLUMN_NAME + ''''
          FROM INFORMATION_SCHEMA.COLUMNS COL2
          WHERE COL1.TABLE_NAME = COL2.TABLE_NAME
          FOR XML PATH ('')
      ) TableColumns (ColumnList)
    WHERE
      1=1 AND 
      COL1.TABLE_NAME = @TableName
)

EXECUTE (@SqlString)

score 4 · Answer 2 · answered Sep 01 '09 at 18:16

try this (sql server 2005 syntax):

DECLARE @YourTable table (col1  varchar(5)
                         ,col2  int
                         ,col3  datetime
                         ,col4  char(3)
                         )

insert into @YourTable values ('abcdf',123,'1/1/2009','aaa')
insert into @YourTable values ('aaaaa',456,'1/2/2009','bbb')
insert into @YourTable values ('bbbbb',789,'1/3/2009','aaa')
insert into @YourTable values ('ccccc',789,'1/4/2009','bbb')
insert into @YourTable values ('aaaaa',789,'1/5/2009','aaa')
insert into @YourTable values ('abcdf',789,'1/6/2009','aaa')


;with RankedYourTable AS
(
SELECT
    ROW_NUMBER() OVER(PARTITION by col1 order by col1) AS col1Rank
        ,ROW_NUMBER() OVER(PARTITION by col2 order by col2) AS col2Rank
        ,ROW_NUMBER() OVER(PARTITION by col3 order by col3) AS col3Rank
        ,ROW_NUMBER() OVER(PARTITION by col4 order by col4) AS col4Rank
    FROM @YourTable
)
SELECT
    SUM(CASE WHEN      col1Rank=1 THEN 1 ELSE 0 END) AS col1DistinctCount
        ,SUM(CASE WHEN col2Rank=1 THEN 1 ELSE 0 END) AS col2DistinctCount
        ,SUM(CASE WHEN col3Rank=1 THEN 1 ELSE 0 END) AS col3DistinctCount
        ,SUM(CASE WHEN col4Rank=1 THEN 1 ELSE 0 END) AS col4DistinctCount
    FROM RankedYourTable

OUTPUT:

col1DistinctCount col2DistinctCount col3DistinctCount col4DistinctCount
----------------- ----------------- ----------------- -----------------
4                 3                 6                 2

(1 row(s) affected)

Simple query to help generate yours painlessly:SELECT 'ROW_NUMBER() OVER(PARTITION BY ' + COLUMN_NAME + ' ORDER BY ' + COLUMN_NAME + ') AS ' + COLUMN_NAME + 'Rank,', 'SUM(CASE WHEN ' + COLUMN_NAME + 'Rank=1 THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + 'DistinctCount,' FROM information_schema.COLUMNS WHERE TABLE_NAME='YourTable'; — Ludovic Aubert, Jul 08 '19 at 13:59

score 0 · Answer 3 · answered Sep 01 '09 at 17:14

0

and it's hardcoded.

It is not hardcoding to provide a field list for a sql statement. It's common and acceptable practice.

answered Sep 01 '09 at 17:14

Amy B

108,202
21
135
185

...As is programmatically creating the SQL, as far as it goes (providing your users never supply the values you put in it - fine in this question, you have the column list somewhere). – ijw Sep 01 '09 at 17:18
if I was going to code-gen some SQL, and if I was on MSSqlServer, I'd check out sysobjects and syscolumns. – Amy B Sep 01 '09 at 17:23
If you want to write the code in a more portable way, you should select from Information_Schema.Tables and Information_Schema.Columns, rather than selecting from sysobjects and syscolumns – Kibbee Sep 01 '09 at 17:51
What I would like to do is find the number of distinct values in each column without knowledge of the column names. I can see how using information_schema.tables would be useful here (I'm using MySQL.) So you would generate the query based on looking at the info_schema.tables table rather than write a query that finds the column names, groups by them, and then counts the values? That should work well enough. – Ryan Sep 01 '09 at 18:26

score 0 · Answer 4 · answered Sep 01 '09 at 18:01

0

This won't necessarily be possible for every field in a table. For example, you can't do a DISTINCT against a SQL Server ntext or image field unless you cast them to other data types and lose some precision.

answered Sep 01 '09 at 18:01

MartW

12,348
3
44
68

Good point. I shouldn't have to worry about this. The fields will only be text or numbers. – Ryan Sep 01 '09 at 18:29

score 0 · Accepted Answer · answered Sep 03 '09 at 15:20

I appreciate all of the responses. I think the solution that will work best for me in this situation (counting the number of distinct values in each column of a table from an external program that has no knowledge of the table except its name) is as follows:

Run "describe table1" and pull out the column names from the result.

Loop through the column names and create the query to count the distinct values in each column. The query will look something like "select count(distinct columnA), count(distinct columnB), ... from table1".

SeanLi · Answer 6 · 2022-06-27T04:55:46.857

Raj More's answer works well if you don't need to consider null as a value as count(distinct...) does not count null. Here is a modification to count values including null by converting values to a string and replacing null with "NULL AS SOME IMPOSSIBLE STRING":

DECLARE @TableName VarChar (1024) = 'tableName'
DECLARE @SqlString VarChar (Max)

set @SqlString = (
  SELECT DISTINCT
    'SELECT ' + 
        RIGHT (ColumnList, LEN (ColumnList)-1) + 
      ' FROM ' + Table_Name
    FROM INFORMATION_SCHEMA.COLUMNS COL1
      CROSS AppLy (
        SELECT ', COUNT (DISTINCT coalesce(cast([' + COLUMN_NAME + '] as varchar),
          ''NULL AS SOME IMPOSSIBLE STRING'')) AS ' + '''' + COLUMN_NAME + ''''
          FROM INFORMATION_SCHEMA.COLUMNS COL2
          WHERE COL1.TABLE_NAME = COL2.TABLE_NAME
          FOR XML PATH ('')
      ) TableColumns (ColumnList)
    WHERE
      COL1.TABLE_NAME = @TableName
)

EXECUTE (@SqlString)

score -2 · Answer 7 · answered Sep 01 '09 at 17:47

-2

DISTINCT is evil. Do COUNT/GROUP BY

answered Sep 01 '09 at 17:47

Cristian Cotovan

1,090
1
13
23

Please qualify this with more information. How is using distinct evil when compred to doing COUNT/GROUP BY? – Kibbee Sep 01 '09 at 17:49
DISTINCT behaves erratically with larger datasets and from platform to platform. At least in my experience. I find grouping results to be more predictable, particularly if you deal with differently encoded data, UTF, etc. – Cristian Cotovan Sep 01 '09 at 17:55
I'll have to look into using group by. – Ryan Sep 01 '09 at 18:29
count/group by would only get distict count for a single column. For columns A and B you would end up with two selects, because select A, B, count(*) from ... group by A, B would give you counts of the distinct pair (A, B), not distinct A and distinct B. The OP is on the right track with count(distinct A), count(distinct B) – Shannon Severance Sep 01 '09 at 19:40

SQL: count number of distinct values in every column

7 Answers7

Linked