Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: wrong result for summing up operation

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    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.
    Last edited by cy163; 12-25-08 at 21:57.

  4. #4
    Join Date
    Apr 2007
    Posts
    130
    I have found out that it is a mistake in my C program caused this problem. It is nothing to do with SQL statements and MySQL C APIs.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the feedback

    you must be exhausted, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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