Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007

    Unanswered: Why summing integer fields generating floats

    After I run the following SQL statement, I can see the float type value, like 19.27821, for the Score field. Why, How to avoid this. THanks.

               Table2.DocID, SUM(2*(Table2.Freq + Table1.Freq)) AS Score
               Table1, Table2
            Table1.Word =  Table2.Word
       GROUP BY Table2.DocID ORDER BY Score;

    Table 1
    Word    Freq
    Book      2
    Desk      3
    Pen        3
    Board      3
    DocID   Word    Freq
    1         Book     3
    1         English    2
    1          Math     1
    2         Desk       2
    2         Machine    5
    2         Power      2
    3         Desk       3
    3         Teacher    3
    3         Class       2
    4         Building    1
    4         Tower     2
    Last edited by cy163; 11-20-08 at 08:25.

  2. #2
    Join Date
    Nov 2008
    sorry, I do not have immediate access to MySQL. I am on SQL Server now.

    Pl try this:
    declare @var1 int, @var2 int

    set @var1 = 5
    set @var2 = 6

    select sum(2 *(@var1 + @var2)) as Score

    and then try this:
    select sum((@var1 + @var2)) as Score

    If there is no difference, I have to try it on MySQL, else I can see that a Constant like 2 may be internally declared as a decimal/float rather than an int, and when you sum the result is converted to the higher datatype, which is normal practice in C type languages.

    To anyway convert to INT, you can try the equivalent of CONVERT, like this
    select convert(INT,sum(2 *(@var1 + @var2))) as Score

    hope it helps

  3. #3
    Join Date
    Apr 2007
    kjambu thanks

    Right now, I have not access to my MySQL database. I will report the result later.

    Yes, I was trying to show the values of 'Score' field in a C++ program using printf(...). About this, I have another question, the values of the 'Score' field can only be displayed normally with the option '%s', but not with '%f' and '%d'. Is this normal?

  4. #4
    Join Date
    Jun 2007
    Testing your query with your data, using your SQL produces the results
    | DocID | Score |
    |     1 |    10 |
    |     2 |    10 |
    |     3 |    12 |
    3 rows in set (0.00 sec)
    There is no 19.27821 in the results.

    I created the tables using int for Freq but I assume you did this yourself.

    Would it be worth actually testing your queries a little better yourself?

  5. #5
    Join Date
    Apr 2007
    mike_bike_kite thanks for your reply.

    The real data (for table2) has the same schema as shown in the example table, but contains several million records.
    Some of the Score values are shown as float- or double- type values, like 19.27821.

  6. #6
    Join Date
    Nov 2008
    Ahh ha, I thought so, you are trying to print in C++
    In those languages, a constant is definitely not stored as INT
    But I don't see why you cant use %d
    i am sure, you should be able to do that.

  7. #7
    Join Date
    Apr 2007
    kjambu, thanks

    Yes, I tried to print the values of Score fields. when using printf with %d, the values could not be displayed normally. the values like 19.27821 were printed with %s. Strange Strange Strange.

  8. #8
    Join Date
    Nov 2008
    i worked on C/C++ atleast 15 years ago. So I have no C compiler extra on my machine. But I can give you a small suggestion. Try to cast it like this
    (int) Score, and then use %d.

    I know you have solved the issue use %s, but as a learning, there is no harming trying it.

Posting Permissions

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