Results 1 to 2 of 2

Thread: Joins

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Joins

    Hi all...Using joins, i want to get SUM(salary) by combining both employee and employees table.
    Look my table structure below:

    Code:
    SQL> select * from employee;
    
         EMPNO ENAME           HIREDATE  ORIG_SALARY     SALARY R        MGR     DEPTNO
    ---------- --------------- --------- ----------- ---------- - ---------- ----------
             1 Jason           25-JUL-96        1234       8767 E          2         10
             2 John            15-JUL-97        2341       3456 W          3         20
             3 Joe             25-JAN-86        4321       5654 E          3         10
             4 Tom             13-SEP-06        2413       6787 W          4         20
             5 Jane            17-APR-05        7654       4345 E          4         10
             6 James           18-JUL-04        5679       6546 W          5         20
             7 Jodd            20-JUL-03        5438       7658 E          6         10
             8 Joke            01-JAN-02        8765       4543 W                    20
             9 Jack            29-AUG-01        7896       1232 E                    10
    
    9 rows selected.
    
    SQL> select * from employees;
    
         EMPNO ENAME    INIT  JOB             MGR BDATE         SALARY       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.
    
    SQL> With me as
      2  ( select deptno,salary from employee where deptno = 10
      3    union
      4    select deptno,salary from employees where deptno = 10)
      5   select deptno,sum(salary) from me
      6   group by deptno;
    
        DEPTNO SUM(SALARY)
    ---------- -----------
            10       30056
    
    1 row selected.
    
    SQL> With me as
      2  ( select deptno,salary from employee where deptno = 20
      3    union
      4    select deptno,salary from employees where deptno = 20)
      5    select deptno,sum(salary) from me
      6    group by deptno;
    
        DEPTNO SUM(SALARY)
    ---------- -----------
            20       27132
    
    1 row selected.
    
    SQL>  select deptno,sum(salary) from employees where deptno = 30
      2  group by deptno;
    
        DEPTNO SUM(SALARY)
    ---------- -----------
            30        6300
    
    1 row selected.
    
    SQL> select deptno,sum(salary) from employees where deptno = 40
      2  group by deptno;
    
        DEPTNO SUM(SALARY)
    ---------- -----------
            40        4300
    
    1 row selected.
    Above, i used separate queries to get the result of SUM(salary) by deptno.
    Here, I want a single query to get SUM(salary) with deptno.

    I mean , answer to be like this...

    Code:
    deptno         Sum(salary)
    ----------------------------
           10       30056
           20       27132
           30        6300
           40        4300
    Last edited by satheeshsharma; 07-29-13 at 06:53.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from which table is the SALARY column that is to bu summed?

    since "SALARY" exists in two tables, the problem statement is ambiguous & "design" is flawed
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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