How to count number of rows with unique values in each column given following query in SQL

Question

SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'MYTABLE' AND OWNER = 'MYSCHEMA'

So right now this displays each column present in my table but I want to also display the number of rows in each of these columns.

How would I specifically go about doing this?

Does this answer your question? [SQL count rows in a table](https://stackoverflow.com/questions/28916917/sql-count-rows-in-a-table) — Adam Iqshan, Jan 16 '20 at 21:01
It would be as simple as to count the number of rows of the table, unless you need something else you haven't explained well. Please add some sample data and the expected result. — The Impaler, Jan 16 '20 at 21:03

Littlefoot · Accepted Answer · 2020-01-16T21:13:14.327

Number of rows is related to tables, not columns. So you'd

select count(*) from your_table;

to get that value.

Dynamically, a PL/SQL could do it as follows:

SQL> set serveroutput on
SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select table_name, column_name
  5                  from user_tab_columns
  6                  where table_name in ('EMP', 'DEPT')
  7                 )
  8    loop
  9      execute immediate 'select count(distinct ' || cur_r.column_name || ')' ||
 10                        ' from ' ||cur_r.table_name into l_cnt;
 11      dbms_output.put_line(cur_r.table_name ||'.'|| cur_r.column_name ||': ' || l_cnt);
 12    end loop;
 13  end;
 14  /
DEPT.DEPTNO: 4
DEPT.DNAME: 4
DEPT.LOC: 4
EMP.EMPNO: 14
EMP.ENAME: 14
EMP.JOB: 5
EMP.MGR: 6
EMP.HIREDATE: 13
EMP.SAL: 12
EMP.COMM: 4
EMP.DEPTNO: 3

PL/SQL procedure successfully completed.

SQL>

But I want to retrieve the distinct count per column. How would I go about doing that? — asdfghjkl9999, Jan 16 '20 at 21:03
One option is to use dynamic SQL. I modified my answer; have a look, please. — Littlefoot, Jan 16 '20 at 21:13

score 1 · Answer 2 · answered Jan 16 '20 at 21:05

1

You'll need to use some kind of dynamic SQL to compose the query shown below:

select
  count(distinct col1),
  count(distinct col2),
  count(distinct col3),
  ...
from my_table

answered Jan 16 '20 at 21:05

The Impaler

45,731
9
39
76

I have an unknown amount of columns. This process needs to be repeated across multiple different tables with varying sizes – asdfghjkl9999 Jan 16 '20 at 21:06
As I said, you need to *compose* a query using dynamic SQL. – The Impaler Jan 17 '20 at 01:21

How to count number of rows with unique values in each column given following query in SQL

2 Answers2