CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
user2226485
user2226485

Reputation: 11

SQL Server : Getting distinct count on every column in a large view

I have a large SQL Server 2012 database with a couple of views I need to analyse.

What I want to know for each view is the number of unique values of each column in the view. I could not find any script yet that would give me this.

So the input should be the view name and the output would be two rows like:

Column         Uniques
accountid      200
accountname    178
numberofemp     23
telephone      154
notusedyet       0

Upvotes: 1

Views: 579

Answers (2)

Moho
Moho

Reputation: 16523

Get a distinct count for each column via count(distinct [ColA]) for each column you want to count (no group by). You can then unpivot to get the tabular format you desire. Here's an example:

;with DistinctColumnCount( Id, Description )
as
(
    select
        count(distinct Id) Id
        , count(distinct Description) Description
    from
        EntityB
)

SELECT CountColumn, [Count].[DistinctCount]
FROM 
    DistinctColumnCount
unpivot
    ( DistinctCount for CountColumn in ( Id, [Description] ) ) as [Count]

Upvotes: 2

John Woo
John Woo

Reputation: 263763

You need to use COUNT() (an aggregate function) with Distinct to count only the unique values.

SELECT  [column], COUNT(DISTINCT value) [Uniques]
FROM    tableName
GROUP   BY [column]

Upvotes: 2

Related Questions