entre Desarrolladores

Recibe ayuda de expertos

Registrate y pregunta

Es gratis y fácil

Recibe respuestas

Respuestas, votos y comentarios

Vota y selecciona respuestas

Recibe puntos, vota y da la solución

Pregunta

2votos

DUDA CON CONSULTA IN-LINE

Hola, tengo un problemilla con una consulta de sql relacionado con las vistas in-line, no busco ni mucho menos que penseis que teneis que resolverme la cuestión, es que después de mil vueltas no lo saco, el problema es el siguiente:

Para cada departamento mostrar su media salarial y cuántos empleados ganan menos y más que esa media.

  SELECT EMP.DEPTNO, EMP.AVG(SAL), MAXIMO.MAX, MINIMO.MIN
FROM EMP,
     (SELECT COUNT(ENAME) MAX, DEPTNO FROM EMP GROUP BY ENAME HAVING AVG(SAL)>(SELECT SAL FROM EMP)) MAXIMO,
     (SELECT COUNT(ENAME) MIN, DEPTNO FROM EMP GROUP BY ENAME HAVING AVG(SAL)<(SELECT SAL FROM EMP)) MINIMO,
WHERE MAXIMO.DEPTNO=MINIMO.DEPTNO 
AND 
EMP.DEPTNO=X.DEPTNO

Son de tablas que vienen por defecto pues estoy aprendiendo, la idea que he creído mas correcta es crearme dos tablas con una subconsulta que me guarde una los empleados que ganan mas que la media con el count(*) y otra los que ganen menos, pero no soy capaz de sacar el join que debería hacer o algo se me escapa.

La tabla seria esta por si no la conoceis:


SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB     MGR HIREDATE     SAL       COMM     DEPTNO
     ----------  ----------     ---------          ----------    --------    ----------      ----------      ----------
      7369 SMITH      CLERK           7902 17/12/80        800                           20
      7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
      7566 JONES      MANAGER         7839 02/04/81       2975                       20
      7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400        30
      7698 BLAKE      MANAGER         7839 01/05/81       2850                       30
      7782 CLARK      MANAGER         7839 09/06/81       2450                        10
      7788 SCOTT      ANALYST         7566 09/12/82       3000                    20
      7839 KING       PRESIDENT            17/11/81       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30
      7876 ADAMS      CLERK           7788 12/01/83       1100                    20
      7900 JAMES      CLERK           7698 03/12/81        950                    30
      7902 FORD       ANALYST         7566 03/12/81       3000                    20
      7934 MILLER     CLERK           7782 23/01/82       1300                    10

14 filas seleccionadas.

Muchas Gracias por la ayuda =)

0voto

oskar5445 comentado

Agrego un dato más, puede que uno de mis problemas venga porque no soy capaz de hacer una consulta que me saque solo los mayores a una media salaria es decir:

SELECT COUNT(ENAME) MIN FROM EMP HAVING AVG(SAL)<ANY(SELECT SAL FROM EMP);

Me sale 14 es decir todos los registros y si la media salarial son 1400 tendria que salirme a ojo mínimo 5 pero siempre me salen 14 porque algo hago mal.

Editado: movido a comentario de la pregunta

2 Respuestas

1voto

oskar5445 Puntos700

La solución final queda así, gracias por la ayuda

select L.deptno "Departamento", L.media, J.cn "menos media", L.cn "mas media" 
from 
(select B.deptno, A.av media, count(B.ename) cn 
from emp B, 
(select deptno, avg(sal) av 
from emp
group by deptno) A 
where B.deptno = A.deptno and B.sal > A.av
group by B.deptno,A.av) L,
(select B.deptno, count(B.ename) cn 
from emp B, 
(select deptno, avg(sal) av 
from emp
group by deptno) A 
where B.deptno = A.deptno and B.sal < A.av
group by B.deptno) J
where L.deptno = J.deptno

Ha costado¡¡

2votos

carlossevi Puntos63540

Nunca he utilizado una base de datos de Oracle pero si funciona como me imagino creo que estás haciendo la comparación al revés. La consulta para contar cuantos están por debajo de la media:

SELECT COUNT(ENAME) MIN FROM EMP HAVING SAL<ANY(SELECT AVG(SAL) FROM EMP);

No sé si la sintaxis es correcta porque no la domino, pero la condición debería ser SAL < (SELECT AVG(SAL) FROM EMP) en lugar de AVG(SAL) < (SELECT SAL FROM EMP) que es lo que tienes puesto.

0voto

oskar5445 comentado

Bueno casi asi pero ya es un gran avance muchas gracias, solo cambiaria de lo que me has puesto el having por el where ya que no tocas funciones de fila asi quedaria

SELECT COUNT(ENAME) CONTAR FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP)

Muchas gracias voy a ver que consigo con esta pista =)

0voto

oskar5445 comentado

Ahora lo que he conseguido que creia que lo tenia es para cada departamento la media PERO solo me saca los mayores y menores de la media de todos¡

SELECT EMP.DEPTNO, AVG(SAL), MAXIMO.MAXI, MINIMO.MINI
FROM EMP,
     (SELECT COUNT(ENAME) MAXI FROM EMP WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP)) MAXIMO,
     (SELECT COUNT(ENAME) MINI FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP)) MINIMO
GROUP BY DEPTNO, MAXIMO.MAXI, MINIMO.MINI

Editado: movido a comentario (no es una respueta)

0voto

carlossevi comentado

Si quieres filtrar por la media de cada departamento debes añadir la condición en la subconsulta (SELECT AVG(SAL) FROM EMP) poniendo (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = X).

Por otro lado me da la impresión de que estás utilizando una subconsulta de más que no sería necesaria. Te la reescribo con cómo sería en T-SQL (ya sé que Oracle utiliza PL/SQL, pero no lo domino, a ver si consigues traducirlo) añadiendo alias para que sea menos lioso:

SELECT 
    TABLAEMP.DEPTNO, AVG(TABLAEMP.SAL),
    (SELECT COUNT(ENAME) 
        FROM EMP AS EMP2 
        WHERE EMP2.SAL > (SELECT AVG(EMPMED.EMP) FROM EMP AS EMPMED WHERE EMPMED.DEPTNO = TABLAEMP.DEPTNO)) ENCIMAMEDIA,
    (SELECT COUNT(ENAME) 
        FROM EMP AS EMP2 
        WHERE EMP2.SAL < (SELECT AVG(EMPMED.EMP) FROM EMP AS EMPMED WHERE EMPMED.DEPTNO = TABLAEMP.DEPTNO)) DEBAJOMEDIA,
FROM EMP AS TABLAEMP
GROUP BY DEPTNO

0voto

oskar5445 comentado

Me cuesta entender porque haces una subconsulta en el select antes de from, eso no lo entiendo, para hacerlo mas claro consegui solucionarlo pero no es eficiente si hubiese muchas tablas, la consuta quedaria así:

SELECT EMP.DEPTNO, AVG(SAL), MAXIMO.MAXI, MINIMO.MINI
FROM EMP,
     (SELECT COUNT(ENAME) MAXI FROM EMP WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10)) MAXIMO,
     (SELECT COUNT(ENAME) MINI FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10)) MINIMO
WHERE EMP.DEPTNO=10
GROUP BY DEPTNO, MAXIMO.MAXI, MINIMO.MINI
UNION
SELECT EMP.DEPTNO, AVG(SAL), MAXIMO.MAXI, MINIMO.MINI
FROM EMP,
     (SELECT COUNT(ENAME) MAXI FROM EMP WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20)) MAXIMO,
     (SELECT COUNT(ENAME) MINI FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20)) MINIMO
WHERE EMP.DEPTNO=20
GROUP BY DEPTNO, MAXIMO.MAXI, MINIMO.MINI
UNION
SELECT EMP.DEPTNO, AVG(SAL), MAXIMO.MAXI, MINIMO.MINI
FROM EMP,
     (SELECT COUNT(ENAME) MAXI FROM EMP WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30)) MAXIMO,
     (SELECT COUNT(ENAME) MINI FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30)) MINIMO
WHERE EMP.DEPTNO=30
GROUP BY DEPTNO, MAXIMO.MAXI, MINIMO.MINI

Es un lio si se mira asi pero lo que hago es sacar el resultado para cada deptartamento

SELECT EMP.DEPTNO, AVG(SAL), MAXIMO.MAXI, MINIMO.MINI
FROM EMP,
     (SELECT COUNT(ENAME) MAXI FROM EMP WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10)) MAXIMO,
     (SELECT COUNT(ENAME) MINI FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10)) MINIMO
WHERE EMP.DEPTNO=10
GROUP BY DEPTNO, MAXIMO.MAXI, MINIMO.MINI

Y con la funcion UNION hacerlo para cada departamento a mano y el resultado me quedaría así:

DEPTNO    AVG(SAL)       MAXI       MINI
-------             ----------           ---------- ----------
     10         2916,66667          4         10
     20         2175                    6          8
     30        1566,66667           7          7

Mas o menos que vendría a ser para el deptno 10 con media 2916,66667 hay 6 empleados que ganan mas y 10 que ganan menos que la media.

0voto

carlossevi comentado

Repito: no tengo el conocimiento suficiente sobre PL/SQL para saber si se parece mucho o poco a T-SQL, pero al fin y al cabo ambos son implmentaciones de SQL así que muy diferentes no pueden ser. En T-SQL lo que estás haciendo es un lío tremendo.

La subconsulta la pongo antes del FROM porque según la estructura básica de una consulta SELECT SQL es donde se colocan los datos que se quieren obtener.

SELECT
** datos a obtener **
FROM
** de dónde se obtienen **
WHERE
** condiciones para obtenerlos **

Como lo que quieres obtener es un conteo basado en una sobconsulta, la subconsulta se pone en el primer bloque y se utilizan ALIAS para distinguir bien las tablas a las que se hacen referencia.

0voto

oskar5445 comentado

Entiendo que no sea tu campo y te agradezco la ayuda bastante, el probelma que esta consulta me causa fuera de todos los lios posibles es que es una consulta IN-LINE y tengo que crear tablas temporales para luego hacer un join con el deptno de la tabla empleados con alguna temporal para que para cada departamento me muestre quien gana mas o menos pero no puedo hacer el join porque si agrego el campo deptno en cualquiera de las temporales tendria que hacer el group by por deptno y ya se me va de las manos. Pero seguiré investigando a ver que conseguimos.

Muchas Gracias

0voto

carlossevi comentado

Ahhhh he leido un poco de documentación y ya entiendo lo que dices y la sintaxis que utilizas. Son tablas similares a las "vistas" pero que se generan durante la propia consulta y se utilizan para consultar sobre ellas.

De hecho en la documentación viene un ejemplo de código que se parece mucho a tu interés:

SELECT a.last_name, a.salary, a.department_id, b.maxsal
  FROM employees a,
       ( SELECT department_id, max(salary) maxsal
         FROM employees
         GROUP BY department_id ) b
 WHERE a.department_id = b.department_id
   AND a.salary = b.maxsal;

Adaptando esa idea a lo que buscas, me queda así:

SELECT
    a.DEPTNO,
    b.AVGSAL,
    c.NUMAX,
    d.NUMMIN    
FROM EMP a,
    (SELECT DEPTNO, avg(SAL) AVGSAL         
        FROM EMP
        GROUP BY DEPTNO ) b,
    (SELECT count(EMPNO) NUMMAX         
        FROM EMP
        WHERE SAL > b.AVGSAL) c,
    (SELECT count(EMPNO) NUMMIN         
        FROM EMP
        WHERE SAL < b.AVGSAL) d,        
WHERE
    a.DEPTNO = b.DEPTNO AND
    a.DEPTNO = c.DEPTNO AND
    a.DEPTNO = d.DEPTNO

Lógicamente no lo he comprobado =)

0voto

oskar5445 comentado

Ahi has llegado a mi fallo y a mi problema, no se puede hacer un JOIN del campo depotno de una tabla temporal que no tenga el campo deptno y el problema es que si lo ponemos habria que hacer un group by y todo se descuadra, al final será facil de hacer pero esta costando¡

Por favor, accede o regístrate para responder a esta pregunta.

Otras Preguntas y Respuestas


...

Bienvenido a entre Desarrolladores, donde puedes realizar preguntas y recibir respuestas de otros miembros de la comunidad.

Conecta