Why summing integer fields generating floats

cy163 · #1 (**permalink**) 11-20-08, 07:42

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.

Code:

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

Code:

Table 1
Word    Freq
Book      2
Desk      3
Pen        3
Board      3





Table2
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

kjambu · #2 (**permalink**) 11-20-08, 08:37

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

cy163 · #3 (**permalink**) 11-20-08, 08:48

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?

mike_bike_kite · #4 (**permalink**) 11-20-08, 08:50

Testing your query with your data, using your SQL produces the results

Code:

+-------+-------+
| 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?

cy163 · #5 (**permalink**) 11-20-08, 09:14

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.

kjambu · #6 (**permalink**) 11-20-08, 09:24

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.

cy163 · #7 (**permalink**) 11-20-08, 09:31

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.

kjambu · #8 (**permalink**) 11-20-08, 10:45

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech