#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0

    How to display the result from 2 queries?


    Hello, first of all thanks for opening this thread and trying to help me, I am trying to display the results from 2 queries, one is suposed to display the count of the employees, per department, who win over the average of the entire company and the other one is suposed to display the count of the employees, per department, who win under the average of the entire company.

    I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them?

    I tried assigning names to each salary using AS but it only displays the first I put in.

    If anyone could please help me I would apretiate it very much, thanks in advance, I will paste the code bellow.


    sql Code:
    (
    SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES
    FROM DEPARTMENTS DE, EMPLOYEES EM
    WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
    AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM)
    GROUP BY DE.DEPARTMENT_NAME
    )
    UNION ALL
    (
    SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MENORES
    FROM DEPARTMENTS DE, EMPLOYEES EM
    WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
    AND EM.SALARY < (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM)
    GROUP BY DE.DEPARTMENT_NAME
    )
    ORDER BY DEPARTMENT_NAME;
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    854
    Rep Power
    387

    Wink


    Originally Posted by miguelosaurio
    . . . Etc ...
    If anyone could please help me I would apretiate it very much, thanks in advance, I will paste the code bellow.
    Two ways:
    1)
    sql Code:
    SELECT   'Mayores:' emp_type, de.department_name, COUNT (em.employee_id) AS num_emp
        FROM departments de, employees em
       WHERE de.department_id = em.department_id
         AND em.salary > (SELECT AVG (em.salary)
                            FROM employees em)
    GROUP BY de.department_name
    UNION ALL
    SELECT   'Menores:', de.department_name, COUNT (em.employee_id)
        FROM departments de, employees em
       WHERE de.department_id = em.department_id
         AND em.salary < (SELECT AVG (em.salary)
                            FROM employees em)
    GROUP BY de.department_name
    ORDER BY department_name;

    2)
    sql Code:
     
    SELECT de.department_name
         , SUM (CASE WHEN em.salary > s.avg_sal THEN 1 ELSE 0 END) AS mayores
         , SUM (CASE WHEN em.salary < s.avg_sal THEN 1 ELSE 0 END) AS menores
        FROM departments de
           , employees em
           , (SELECT AVG (em.salary) avg_sal
                FROM employees em) s
       WHERE de.department_id = em.department_id
    GROUP BY de.department_name
    ORDER BY department_name;

    Comments on this post

    • miguelosaurio agrees
    Last edited by LKBrwn_DBA; October 26th, 2012 at 02:21 PM.
  4. #3
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    854
    Rep Power
    387

    Cool


    And yet another:
    sql Code:
    SELECT   department_name, MAX (mayores) mayores, MAX (menores) menores
        FROM (SELECT   de.department_name, COUNT (em.employee_id) AS mayores, 0 AS menores
                  FROM departments de, employees em
                 WHERE de.department_id = em.department_id
                   AND em.salary > (SELECT AVG (em.salary)
                                      FROM employees em)
              GROUP BY de.department_name
              UNION ALL
              SELECT   de.department_name, 0, COUNT (em.employee_id)
                  FROM departments de, employees em
                 WHERE de.department_id = em.department_id
                   AND em.salary < (SELECT AVG (em.salary)
                                      FROM employees em)
              GROUP BY de.department_name)
    GROUP BY department_name
    ORDER BY department_name;

    Comments on this post

    • miguelosaurio agrees
    Last edited by LKBrwn_DBA; October 26th, 2012 at 02:20 PM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0
    Thanks to the both of you all three solutions worked wonderfully and it actually helped me to solve another problem that I was encountering that I had no clue how to solve.

    Thanks again.

IMN logo majestic logo threadwatch logo seochat tools logo