CODEWITHSUNDEEP
oracle-databasedistinct
user1900474
user1900474

Reputation: 23

Oracle - distinct values of every column

I know there have been some discussions on similar topics but this is a bit different

I need a query in Oracle to get value pairs (column name, count of distinct values) for a given table.

Example: fro this table 

|   ID     |    NAME    |    AGE   |
____________________________________
|   01     |    MARY    |    10    |
|   02     |    MAX     |    30    |
|   03     |    ALICE   |    30    |
|   04     |    MARY    |    20    |
|   05     |    JOE     |    10    |
____________________________________

I need to get

|   COLUNL |    DIST. VALUES    |
________________________________
|   ID     |    5       |
|   NAME   |    4       | 
|   AGE    |    3       |
________________________________

The problem is that I won't know the structure of the table. I'll have just table names and I'll need to generate this information for each of them. Any ideas?????

Upvotes: 2

Views: 6373

Answers (4)

wolφi
wolφi

Reputation: 8361

The number of distinct values per column is stored in the system table USER_TAB_COL_STATISTICS. The stats are gathered by calling the procedure DBMS_STATS.GATHER_TABLE_STATS. Ask your DBA, it should be set up already.

CREATE TABLE mytable (id NUMBER, name VARCHAR2(10), age NUMBER);
INSERT INTO mytable VALUES (01, 'MARY',  10);
INSERT INTO mytable VALUES (02, 'MAX',   30);
INSERT INTO mytable VALUES (03, 'ALICE', 30);
INSERT INTO mytable VALUES (04, 'MARY',  20);
INSERT INTO mytable VALUES (04, 'JOE',   10);
COMMIT;
EXECUTE dbms_stats.gather_table_stats(user, 'MYTABLE');

SELECT table_name, column_name, num_distinct
  FROM user_tab_col_statistics
 WHERE table_name = 'MYTABLE';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
MYTABLE    ID          4
MYTABLE    NAME        4
MYTABLE    AGE         3

Upvotes: 7

Florin Ghita
Florin Ghita

Reputation: 17643

Below the ideea, you should work on it a little:

create table stats_on_tables(
    date_id date, 
    table_name varchar2(32), 
    col_name varchar2(32), 
    cnt_distinct number
);

and then create a procedure with parameter p_table_name that:

for n in (select column_name from user_tab_columns where table_name = p_table_name)
loop
  execute immediate 
    'insert into stats_on_table (date_id, table_name, col_name, cnt_distinct)
     values (sysdate, '||p_table_name||','||n.column_name||', (
     select count(distinct '||n.column_name||') from '||p_table_name||' ))';
end loop;
commit;

Upvotes: 1

Ciarán
Ciarán

Reputation: 3057

You can do this using a UNION, Eg...

 SELECT 'ID', Count(Distinct ID) As ValueCount From TableName
 UNION
 SELECT 'NAME', Count(Distinct NAME) From TableName
 UNION
 SELECT 'AGE', Count(Distinct AGE) From TableName

[Edit]
Another way would be to ANALYSE the table, Eg...

ANALYZE TABLE TABLENAME COMPUTE STATISTICS;

And then...

SELECT COLUMN_NAME, NUM_DISTINCT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLENAME'

Upvotes: 0

Taryn
Taryn

Reputation: 247860

You can use a UNION ALL query to UNPIVOT and then count the data:

select col col_name,
  count(distinct value)
from
(
  select cast(id as varchar(10)) value, 'id' col
  from yourtable
  union all
  select name value, 'name' col
  from yourtable
  union all
  select cast(age as varchar(10)) value, 'age' col
  from yourtable
) 
group by col

See SQL Fiddle with Demo

If you are using Oracle 11g+, then you can use the UNPIVOT function to perform this:

select col, count(distinct value) CountofValue
from
(
  select cast(id as varchar(10)) id, 
    name, 
    cast(age as varchar(10)) age
  from yourtable
) 
unpivot
(
  value
  for col in (id, name, age)
) 
group by col

See SQL Fiddle with Demo

Upvotes: 0

Related Questions