Data Densification /Data repetition in sql

Data Densification 



Example1:

SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;

DEPTNO                 DEPT_COUNT             
---------------------- ---------------------- 
20                     5                      
30                     6    

The above query returns departments and their employee count, what if we want to 

repeat the count for all employees of corresponding dept's.

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6
 

Example2:

We had a requirement where in even if the data has gaps the report should be continuous , regardless of presence of data.     There are two tables below.                                              

       

Expected Output:

Query:

select Pen_NAme,Time_id,nvl(sum(quantity),0) from temp ym

LEFT OUTER JOIN

Gags co PARTITION BY (co.pen_name)

ON (co.procurement = ym.time_id)

group by Pen_NAme,Time_id;