Results 1 to 7 of 7

Thread: subquery

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: subquery

    Code:
    SQL> select * from employees;
    
         EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM     DEPTNO
    ---------- -------- ----- -------- ---------- --------- ---------- ---------- ----------
             1 Jason    N     TRAINER           2 18-DEC-65        800                    10
             2 Jerry    J     SALESREP          3 19-NOV-66       1600        300         10
             3 Jord     T     SALESREP          4 21-OCT-67       1700        500         20
             4 Mary     J     MANAGER           5 22-SEP-68       1800                    20
             5 Joe      P     SALESREP          6 23-AUG-69       1900       1400         30
             6 Black    R     MANAGER           7 24-JUL-70       2000                    30
             7 Red      A     MANAGER           8 25-JUN-71       2100                    40
             8 White    S     TRAINER           9 26-MAY-72       2200                    40
             9 Yellow   C     DIRECTOR         10 27-APR-73       2300                    20
            10 Pink     J     SALESREP            28-MAR-74       2400          0         30
    
    10 rows selected.
    Code:
    SQL> select * from employee;
    
    ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
    ---- ---------- ---------- --------- --------- ---------- ---------- ---------------
    01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
    02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
    03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
    04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
    05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
    06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
    07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
    08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
    
    8 rows selected.
    For example:

    Code:
    SQL> select ( select avg(salary) from employee), (select avg(msal) from employees) from dual;
    
    (SELECTAVG(SALARY)FROMEMPLOYEE) (SELECTAVG(MSAL)FROMEMPLOYEES)
    ------------------------------- ------------------------------
                          4071.7525                           1880
    Here, i got the average salaries for both employees and employee in a single query...

    But wat i need is.,

    I want to get the output in a single query by adding both the average salaries from employee and employees.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't understand what you want, sorry. What would be the result?

  3. #3
    Join Date
    May 2013
    Posts
    33
    For the above tables,

    The average salary for employee is 4071.7525
    The average msal for employees is 1880.

    I want the average of 4071.7525 + 1880..I mean the average of employees and employee is a single query.

    Hope u get me now?
    Last edited by satheeshsharma; 07-03-13 at 07:54.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you want to get the average of 4071.7525 and 1880?
    or
    ( SUM(salary) + SUM(msal) ) / ( COUNT(salary) + COUNT(msal) )

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This?
    Code:
    with all_of_them as
      (select msal   sal from employees
       union all
       select salary sal from employee
      )
    select avg(sal) 
    from all_of_them;

  6. #6
    Join Date
    May 2013
    Posts
    33
    I want the average of 4071.7525 and 1880 by querying both the tables in a single query.

    For example;
    Code:
    SQL> select  4071.7525 + 1880 from dual;
    
    4071.7525+1880
    --------------
         5951.7525
    
    SQL> select 5951.7525/2 from dual;
    
    5951.7525/2
    -----------
     2975.87625

    I want the answer to be 2975.87625.

  7. #7
    Join Date
    May 2013
    Posts
    33
    Thanks to all my dear friends....
    Finally got the output with the help of u all...

    Code:
    SQL> with result as
      2    ( select avg(salary)   sal from employee
      3      union
      4      select avg(msal)   sal from employees)
      5  select avg(sal) from result;
    
      AVG(SAL)
    ----------
    2975.87625

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •