wrong result for summing up operation

cy163 · #1 (**permalink**) 12-25-08, 08:44

Hello ALL,

I have two tables;

Code:

tb1
Name Salary(INT)  Dept.
Bob 1000      Sale
Tom 2000     Finance
John 3000     Sale
Winson 4000  Sale

tb2
Name Bonus(INT)
Bob 100
Tom 200

I did the query

Code:

SELEC 
        tb1.Name, SUM(CASE WHEN tb1.Dept='Sale' THEN 0.67 
        WHEN tb1.Dept='Finance' THEN 0.88 ELSE 1 END          
 *(tb1.Salarytb1.Salary + COALESCE(tb2.Bonus,0))) Total 
From 
        tb1
LEFT JOIN 
        tb2
ON
       tb1.Name = tb2.Name

   group by tb1.Name order by Total

However, I got wrong number for 'Total'. Why?
Both Salary and Bonus are INT type fields.

r937 · #2 (**permalink**) 12-25-08, 09:21

Quote:

Originally Posted by cy163

However, I got wrong number for 'Total'. Why?

why?

i'll tell you why

because once again you have faked your real problem

here is the answer to the fake problem you posted

the detailed calculations look like this:

Code:

SELECT tb1.Name
     , CASE WHEN tb1.Dept='Sale' THEN 0.67
            WHEN tb1.Dept='Finance' THEN 0.88
            ELSE 1 END  AS rate
     , tb1.Salary
     , COALESCE(tb2.Bonus,0) AS bonus
     , CASE WHEN tb1.Dept='Sale' THEN 0.67
            WHEN tb1.Dept='Finance' THEN 0.88
            ELSE 1 END
      * ( tb1.Salary
      + COALESCE(tb2.Bonus,0) ) AS Subtotal
  FROM tb1
LEFT OUTER
  JOIN tb2
    ON tb2.Name = tb1.Name


Name   rate  Salary  bonus  Subtotal
Bob    0.67  1000     100     737
Tom    0.88  2000     200    1936
John   0.67  3000       0    2010
Winson 0.67  4000       0    2680

and here are the totals:

Code:

SELECT tb1.Name
     , SUM(
       CASE WHEN tb1.Dept='Sale' THEN 0.67
            WHEN tb1.Dept='Finance' THEN 0.88
            ELSE 1 END
      * ( tb1.Salary
      + COALESCE(tb2.Bonus,0) ) ) AS Total
  FROM tb1
LEFT OUTER
  JOIN tb2
    ON tb2.Name = tb1.Name
GROUP 
    BY tb1.Name 
ORDER 
    BY Total

Name   Total
Bob     737
Tom    1936
John   2010
Winson 2680

so: the answer is, your query does work correctly

have a happy holiday

cy163 · #3 (**permalink**) 12-25-08, 20:42

thanks r937 for your seasonal greeeting.

Merry Christmas and Happy New Year to you. Thank you for your help in 2008.

Now I realized that i can get the correct sum result, when i run the query in the mysql command line. However, i get wrong sum result when I execute the statement via mysql C API.