4 Replies Latest reply: Jan 31, 2013 6:18 AM by jeneesh RSS

    Wrong output of sum values

    Chanchal Wankhade
      Hi,

      We are using oracle 10G R2 On windows.

      We have two tables and we want sum of salary of every employ which are commonly present in both the table.
      For that we have query like below :-
      SELECT A.EMPCODE,SUM(A.SALARY)SALARY, SUM(B.SALARY)  SALARY_B , SUM (A.SALARY-B.SALARY) DIFFERENCE
      FROM EMPLOYEE A, EMPLOYEE_LIVE B
      WHERE A.EMPCODE=B.EMPCODE
      AND A.EMPCODE=771
      GROUP BY A.EMPCODE
      HAVING SUM(A.SALARY)> 0 AND SUM(B.SALARY) >0
      
      
      EMPCODE     SALARY           SALARY_B                       DIFFERENCE
      
      771     43551057277.32     5.31300368539281E15     -5.31296013433553E15
      But when we select sum of salary for that employee code it show's different and the query is like
      SELECT EMPCODE ,SUM(SALARY) SALARY FROM EMPLOYEE
      WHERE EMPCODE=771
      GROUP BY EMPCODE;
      
      
      EMPCODE     SALARY                         
      771     76674396.615
      
           
           
      can anybody tell us as why this is happening.

      Edited by: Chanchal Wankhade on Jan 31, 2013 2:30 AM
        • 1. Re: Wrong output of sum values
          Fran
          chanchal,

          "HAVING SUM(A.SALARY)> 0" at first query but in second you ommit it. Probably sum(salary) make the operation with < 0 values.
          • 2. Re: Wrong output of sum values
            Chanchal Wankhade
            Hi Fran,

            After ommiting having clause then also i am getting same result.

            I think that should not be issue.
            • 3. Re: Wrong output of sum values
              Girish Sharma
              create table employee(empcode number,salary number);
              insert into employee values (771,1000);
              insert into employee values (771,2000);
              create table employee_live(empcode number,salary number);
              insert into employee_live values (770,1000);
              insert into employee_live values (770,2000);
              insert into employee_live values (771,500);
              insert into employee_live values (771,1500);
              insert into employee_live values (772,2000);
              SELECT A.EMPCODE,SUM(A.SALARY)SALARY, SUM(B.SALARY)  SALARY_B , SUM (A.SALARY-B.SALARY) DIFFERENCE
               FROM EMPLOYEE A, EMPLOYEE_LIVE B
                WHERE A.EMPCODE=B.EMPCODE
                AND A.EMPCODE=771
                GROUP BY A.EMPCODE
                HAVING SUM(A.SALARY)> 0 AND SUM(B.SALARY) >0;
              
                 EMPCODE     SALARY   SALARY_B DIFFERENCE
              ---------- ---------- ---------- ----------
                     771       6000       4000       2000
              Now, If I says :
              insert into employee_live values (771,600); and then :
              SELECT A.EMPCODE,SUM(A.SALARY)SALARY, SUM(B.SALARY)  SALARY_B , SUM (A.SALARY-B.SALARY) DIFFERENCE
               FROM EMPLOYEE A, EMPLOYEE_LIVE B
                WHERE A.EMPCODE=B.EMPCODE
                AND A.EMPCODE=771
                GROUP BY A.EMPCODE
                HAVING SUM(A.SALARY)> 0 AND SUM(B.SALARY) >0;
              
                 EMPCODE     SALARY   SALARY_B DIFFERENCE
              ---------- ---------- ---------- ----------
                     771       9000       5200       3800
              Why ? Because empcode 771's salary sum is 3000 and in the employee_live table there 3 rows so 3000*3=9000.

              Why are you using above query. The correct one is :
              SELECT A.EMPCODE,A.SALARY,B.SALARY_B,A.SALARY-B.SALARY_B DIFFERENCE
                FROM
                (SELECT MIN(EMPCODE) EMPCODE,SUM(SALARY) SALARY FROM EMPLOYEE WHERE EMPCODE=771) A,
                (SELECT MIN(EMPCODE) EMPCODE,SUM(SALARY) SALARY_B FROM EMPLOYEE_LIVE WHERE EMPCODE=771) B
              /
              
                 EMPCODE     SALARY   SALARY_B DIFFERENCE
              ---------- ---------- ---------- ----------
                     771       3000       2600        400
              
              SQL>
              Regards
              Girish Sharma
              • 4. Re: Wrong output of sum values
                jeneesh
                Is the EMPCODE column unique in both the tables?

                If not, you will get results like this..