In the HR table, there are 107 employees, with 19 distinct JOB_ID and 11 distinct department_id and one Null department_id.
If I want to create three columns.
column 1: list of all distinct department_id including the Null for total of 12 rows.
column 2: count of all distinct job_id in that department_id in column 1.
column 3: count of all distinct job_id over the entire EMPLOYEES table, ie 19 distinct JOB_ID.
Basically I want to return a table like this
The first two columns are pretty easy to get,
select department_id, count(distinct job_id) from employees
group by department_id;
But I can't get the third column with windows function, I tried to use nested window function, but that seems to be based on column2 instead of the entire table.
any help would be greatly appreciated.