CODEWITHSUNDEEP
sqlvertica
kapil meena
kapil meena

Reputation: 37

count distinct values in column for all columns in a tables in single query

A fast and efficient way of counting distinct values in columns for all columns in a table. In single query

Please Note

  1. I have read-only access.
  2. In a single schema I have multiple tables and I want to calculate for some of the tables but not all

Upvotes: 0

Views: 231

Answers (2)

marcothesane
marcothesane

Reputation: 6749

I assume the most difficult case - when you can't rely on a substring of the table name.

In that case - create your schema/table list, and use it to create a script that builds one big script that you can finally launch once it's generated:

CREATE LOCAL TEMPORARY TABLE srch(table_schema,table_name) 
ON COMMIT PRESERVE ROWS AS
          SELECT 'public','gen_sample'
UNION ALL SELECT 'public','d_product'
UNION ALL SELECT 'dbadmin','d_cust_scd'
UNION ALL SELECT 'dbadmin','currencies'
;

SELECT
    CASE ROW_NUMBER() OVER(ORDER BY c.table_schema,c.table_name,ordinal_position)
    WHEN 1 THEN ''
    ELSE 'UNION ALL '
    END
  ||'SELECT '''||c.table_schema||'.'||c.table_name||'.'||column_name||''','
  ||'COUNT(DISTINCT '||column_name||') FROM '||c.table_schema||'.'||c.table_name
FROM columns c JOIN srch USING(table_schema,table_name);

-- out  SELECT 'dbadmin.currencies.id',COUNT(DISTINCT id) FROM dbadmin.currencies
-- out  UNION ALL SELECT 'dbadmin.currencies.nm',COUNT(DISTINCT nm) FROM dbadmin.currencies
-- out  UNION ALL SELECT 'dbadmin.currencies.sgn',COUNT(DISTINCT sgn) FROM dbadmin.currencies
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_key',COUNT(DISTINCT cust_key) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_id',COUNT(DISTINCT cust_id) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_from_dt',COUNT(DISTINCT cust_from_dt) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_to_dt',COUNT(DISTINCT cust_to_dt) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_is_curr',COUNT(DISTINCT cust_is_curr) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_cre_ts',COUNT(DISTINCT cust_cre_ts) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_udt_ts',COUNT(DISTINCT cust_udt_ts) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_fname',COUNT(DISTINCT cust_fname) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_lname',COUNT(DISTINCT cust_lname) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_phoneno',COUNT(DISTINCT cust_phoneno) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_loy_lvl',COUNT(DISTINCT cust_loy_lvl) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'dbadmin.d_cust_scd.cust_org_id',COUNT(DISTINCT cust_org_id) FROM dbadmin.d_cust_scd
-- out  UNION ALL SELECT 'public.d_product.prdkey',COUNT(DISTINCT prdkey) FROM public.d_product
-- out  UNION ALL SELECT 'public.d_product.prdid',COUNT(DISTINCT prdid) FROM public.d_product
-- out  UNION ALL SELECT 'public.d_product.start_date',COUNT(DISTINCT start_date) FROM public.d_product
-- out  UNION ALL SELECT 'public.d_product.end_date',COUNT(DISTINCT end_date) FROM public.d_product
-- out  UNION ALL SELECT 'public.d_product.price',COUNT(DISTINCT price) FROM public.d_product
-- out  UNION ALL SELECT 'public.gen_sample.srr_key',COUNT(DISTINCT srr_key) FROM public.gen_sample
-- out  UNION ALL SELECT 'public.gen_sample.seq',COUNT(DISTINCT seq) FROM public.gen_sample
-- out  UNION ALL SELECT 'public.gen_sample.nucleotide',COUNT(DISTINCT nucleotide) FROM public.gen_sample
-- out  UNION ALL SELECT 'public.gen_sample.quality',COUNT(DISTINCT quality) FROM public.gen_sample

Upvotes: 1

vandna makvana
vandna makvana

Reputation: 1

  1. You can use this query for counting distinct value of single column.(refer Query 1)
  2. for multiple column you have to use union all.(refer Query 2)

Query 1 - Select distinct Your_ColumnName, count(*) from Your_TableName group by Your_ColumnName

Query 2 - Select distinct Your_ColumnName1, count() from Your_TableName group by Your_ColumnName1 union all Select distinct Your_ColumnName2, count() from Your_TableName group by Your_ColumnName2

Upvotes: 0

Related Questions