Combinación de valores agregados y no agregados en SQL usando la cláusula Joins y Over

Requisito previo: las funciones agregadas en SQL , las uniones en las funciones agregadas de SQL
realizan un cálculo en un conjunto de valores y devuelven un valor único. Ahora, considere una tabla de empleados EMP y una tabla de departamentos DEPT con la siguiente estructura:

Mesa – MESA DE EMPLEADOS

Nombre	Nulo	Escribe
EMPNO	NO NULO	NÚMERO 4)
NOMBRE		VARCHAR2(10)
TRABAJO		VARCHAR2(9)
MONSEÑOR		NÚMERO 4)
FECHA DE CONTRATACIÓN		FECHA
SAL		NÚMERO (7, 2)
COMUNICACIÓN		NÚMERO (7, 2)
NÚMERO DE DEPARTAMENTO		NÚMERO 2)

Mesa – MESA DE DEPARTAMENTO

Nombre	Nulo	Escribe
NÚMERO DE DEPARTAMENTO		NÚMERO 2)
NOMBRE		VARCHAR2(14)
LOC		VARCHAR2(13)

Y se necesitan los siguientes resultados:

1. MOSTRAR NOMBRE, SAL, TRABAJO DE EMP JUNTO CON MAX, MIN, PROMEDIO, SAL TOTAL DE LOS EMPS QUE REALIZAN EL MISMO TRABAJO.
2. MOSTRAR EL NOMBRE DEL DEPARTAMENTO CON EL NÚMERO DE EMP TRABAJANDO EN ÉL.

Los valores agregados no se pueden usar directamente con valores no agregados para obtener un resultado. Así se pueden utilizar los siguientes conceptos:

1. Uso de uniones:

1. Cree una subtabla que contenga el resultado de los valores agregados.
2. Usando Join, use los resultados de la subtabla para mostrarlos con valores no agregados.

Soluciones para el problema 1 usando JOIN:

SELECT ENAME, SAL, EMP.JOB, 
            SUBTABLE.MAXSAL, SUBTABLE.MINSAL, 
            SUBTABLE.AVGSAL, SUBTABLE.SUMSAL
FROM EMP
INNER JOIN
        (SELECT JOB, MAX(SAL) MAXSAL, MIN(SAL) 
                MINSAL, AVG(SAL) AVGSAL, SUM(SAL) SUMSAL 
         FROM EMP 
          GROUP BY JOB) SUBTABLE
                  ON EMP.JOB = SUBTABLE.JOB; 

Salida para datos de muestra:

Esmalte	Sal	Trabajo	MaxSal	MinSal	Promedio Sal	sumasal
SCOTT	3300	ANALISTA	3300	1925	2841.67	8525
ENRIQUE	1925	ANALISTA	3300	1925	2841.67	8525
VADO	3300	ANALISTA	3300	1925	2841.67	8525
HERRERO	3300	EMPLEADO	3300	1045	1746.25	6985
MOLINERO	1430	EMPLEADO	3300	1045	1746.25	6985

2. Usando la cláusula ‘Over’ –

1. LA CLÁUSULA OVER JUNTO CON PARTITION BY SE UTILIZA PARA DIVIDIR LOS DATOS EN PARTICIONES.
2. LA FUNCIÓN ESPECIFICADA OPERA PARA CADA PARTICIÓN.

Soluciones para el problema 2 usando la cláusula OVER:

SELECT DISTINCT(DNAME),
COUNT(ENAME) OVER (PARTITION BY EMP.DEPTNO) EMP
FROM EMP
RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
ORDER BY EMP DESC; 

nombre	Emp
VENTAS	6
INVESTIGAR	5
CONTABILIDAD	3
OPERACIONES	0
OTROS	0