How to display the result from 2 queries?

Dev Shed Forums Sponsor:

October 21st, 2012, 10:00 PM

miguelosaurio

Registered User

Join Date: Oct 2012

Posts: 7

Time spent in forums: 1 h 15 m 39 sec
Reputation 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:

Original - 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;

October 26th, 2012, 02:05 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

Quote:

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:

Original - 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;

sql Code:

Original - 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;

 
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.

October 26th, 2012, 02:17 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 751

Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348

And yet another:

sql Code:

Original - 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.

October 28th, 2012, 04:55 PM

miguelosaurio

Registered User

Join Date: Oct 2012

Posts: 7

Time spent in forums: 1 h 15 m 39 sec
Reputation 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.

How to display the result from 2 queries?

Developer Shed Advertisers and Affiliates