CODEWITHSUNDEEP
sqlsql-servercountdistinctfrequency
ChrisB
ChrisB

Reputation: 33

SQL Query: Count the number of distinct values in a table

I am trying to create a SQL query to count the number of distinct values in a SQL table. My table has 6 columns:

n1  n2  n3  n4  n5  n6
______________________
3   5   7   9   11  20
3   7   11  15  17  20
3   15  26  28  30  40
15  26  30  40  55  56
3   4   5   9   15  17
17  20  26  28  30  40

And here's the result I am trying to get:

  value  frequency
______________________
    3       4
    4       1
    5       2
    7       2
    9       2
    11      2       
    15      3
    17      3   
    20      3
    26      3
    28      2
    30      3
    40      3
    55      1
    56      1

So basically, I need the query to look at the whole table, take a note of each value that appears, and count the number of times that particular value appears.

Upvotes: 0

Views: 138

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I would recommend cross apply for this purpose:

select v.n, count(*) as frequency
from t cross apply
     (values (n1), (n2), (n3), (n4), (n5), (n6)) v(n)
group by v.n;

cross apply, which implements a lateral join is more efficient than union all for unpivoting data. This is particularly true if your "table" is really a view or complex query.

Upvotes: 2

eshirvana
eshirvana

Reputation: 24568

here is the beautiful use case of UNPIVOT if you are using SQL SERVER or ORACLE:

SELECT 
  [value]
  , count(*) frequency 
FROM 
( select n1,n2,n3,n4,n5,n6 from tablename) p
UNPIVOT ([value] for nums in ( n1,n2,n3,n4,n5,n6 )) as unpvt
GROUP BY [value]
ORDER BY frequency DESC

which is more efficient than Union , if performance matters there.

Upvotes: 1

forpas
forpas

Reputation: 164089

Use UNION ALL to get all the nX column values in 1 column and aggregate:

select value, count(*) as frequency
from (
  select n1 as value from tablename union all
  select n2 from tablename union all
  select n3 from tablename union all
  select n4 from tablename union all
  select n5 from tablename union all
  select n6 from tablename 
) t
group by value

Upvotes: 2

Related Questions