SQL Server Count Number of Distinct Values in Each Column of a Table

SQL: count number of distinct values in every column

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".

SQL to find the number of distinct values in a column

You can use the DISTINCT keyword within the COUNT aggregate function:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

This will count only the distinct values for that column.

get a count of each value from every column in a table SQL Server

You could use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name)
                    FROM   sys.columns 
                    WHERE  object_id = Object_id(@Table)
                    -- concatenate result strings with FOR XML PATH
                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

Which will produce SQL Like the following for a table with two columns (Column1 and Column2)

SELECT 'Column1' AS [Column], 
        CAST([Column1] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column1] 
UNION 
SELECT  'Column2' AS [Column], 
        CAST([Column2] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column2]

---

EDIT

If you want a new result set for each column then use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = (SELECT ' SELECT ' + QUOTENAME(Name) 
                        + ', COUNT(*) AS [Count] FROM ' 
                        + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name) + ';'
                FROM   sys.columns 
                WHERE  object_id = Object_id(@Table)
                -- concatenate result strings with FOR XML PATH
                FOR XML PATH (''));

EXECUTE sp_executesql @SQL;

Which would produce SQL Like:

SELECT  [Column1], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column1];

SELECT  [Column2], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column2];

Column wise Distinct count in SQL Server

Another possible solution is to unpivot the values from each row using VALUES table-value constructor and an APPLY oprator:

SELECT *
FROM [dbo].[Test] t
OUTER APPLY (
   SELECT COUNT(DISTINCT v)
   FROM (VALUES (t.[1]), (t.[2]), (t.[3]), (t.[4]), (t.[5]), (t.[6]), (t.[7])) v (v)
) a (DistinctCount)

Result:

StoreID Barcode 1 2 3 4 5 6 7 DistinctCount
-------------------------------------------
S1      B1      4 4 4 4 5 5 3 3
S1      B2      4 4 0 4 1 4 4 3
S2      B1      0 0 0 0 0 1 1 2

Count the number of distinct values of each row (SQL)

Split out each value into its own row (like it should have been stored in the first place), then union then up and (since union discards duplicates) just count the rows:

select id, description, count(*) unique_pays from (
    select id, description, nvl(pay1, -1) from mytable
    union select id, description, nvl(pay2, -1) from mytable
    union select id, description, nvl(pay3, -1) from mytable
    union select id, description, nvl(pay4, -1) from mytable
    union select id, description, nvl(pay5, -1) from mytable
    union select id, description, nvl(pay6, -1) from mytable
    union select id, description, nvl(pay7, -1) from mytable
    union select id, description, nvl(pay8, -1) from mytable
) x
group by id, description

I changed nulls into -1 so they would participate cleanly in the deduping.

---

Related Topics