CODEWITHSUNDEEP
sqloracle-database
dag06001
dag06001

Reputation: 11

Need SQL query/script to give me a distinct count per column for every column in one table

I need a query/script to just display a distinct count of values in each column of a table. I'm using it to tie out to a legacy report where each column is a two way freq of the column by disctinct count. Something like below:

select distinct field1,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'

select distinct field2,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'

select distinct fieldetc...,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'

Upvotes: 1

Views: 110

Answers (3)

SQLChao
SQLChao

Reputation: 7847

I don't know if this helps as it will still run 117 queries but you won't have to manually create them. Run the query which will return 117 select statements. Copy them and run them to get the counts.

SELECT 'SELECT ''' || COLUMN_NAME || ''' AS ColumnName , COUNT(DISTINCT ' 
  || COLUMN_NAME || ') AS Count FROM ' || Table_Schema || '.' || Table_Name 
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_SCHEMA = 'EBL_CLIENT'
  AND TABLE_NAME = 'EAP_FACT'

Upvotes: 0

SkyWalker
SkyWalker

Reputation: 494

Please find below script for generating SQL query:

declare
  v_col varchar2(64) := 'run_id';
  v_val varchar2(64) := '205572';
  v_table varchar2(64) := 'EAP_FACT';
  v_schema varchar2(64) := 'EBL_CLIENT';
begin
  dbms_output.put_line('select *'||chr(10)||'from (select ');
  for i in (select t.COLUMN_NAME, rownum rn
            from all_tab_columns t 
            where t.TABLE_NAME = upper(v_table)
              and t.OWNER = upper(v_schema)
              and t.COLUMN_NAME <> upper(v_col)
            order by t.COLUMN_ID)
    loop
      dbms_output.put_line('            '||case when i.rn=1 then ' ' else ',' end||
                           'count(distinct '||i.column_name||') '||i.column_name);
    end loop;
  dbms_output.put_line('      from '||v_schema||'.'||v_table||' t where t.'||v_col||' = '''||v_val||''')'
                       ||chr(10)||'unpivot'||chr(10)||'(cnt');
  for i in (select listagg (t.COLUMN_NAME,',') within group (order by t.COLUMN_ID) lst
            from all_tab_columns t 
            where t.TABLE_NAME = upper(v_table)
              and t.OWNER = upper(v_schema)
              and t.COLUMN_NAME <> upper(v_col))
    loop
      dbms_output.put_line('    '||'for col in ('||i.lst||')');
    end loop;
  dbms_output.put_line(')'||chr(10)||'order by cnt desc');
end;

You will get some query like this:

select *
from (select 
             count(distinct t.field1) field1 
            ,count(distinct t.field2) field2
            ,count(distinct t.field3) field3
      from EBL_CLIENT.EAP_FACT t where t.run_id = '205572')
unpivot
(cnt
    for col in (field1,field2,field3)
)
order by cnt desc

And after run this query result will be like this:

col     cnt
field2  5
field1  3
field3  1

Upvotes: 1

Andy_in_Van
Andy_in_Van

Reputation: 331

This would give the results for each column in a single row

select Field1Count = count(distinct field1)
,Field2Count = count(distinct field2)
,fieldetcCount = count(fieldetc)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'

Upvotes: 0

Related Questions