Re: group by _date_
* [EMAIL PROTECTED] [...] > Aha, now I have SUM(ROUND(how_much,2)), this works so far. You should swap these two function calls, the way you are doing it you are summing up the rounded values, instead you should round the sum of the values: ROUND(SUM(how_much),2) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by _date_
* [EMAIL PROTECTED] [...] > Does this mean I can not trust float, decimal etc. for financial purposes? Yes you can, but you need to consider the precission of your calculations and comparisons. How much is 1 dollar divided by 3, if you can only use two digits for decimals? What do you do with the extra cent? (...put it in your own account, if you work in a bank...;)) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by _date_
Thanks, Roger Baklund wrote: * [EMAIL PROTECTED] Hello, I have a table bill like when (date), how_much (float), what (varchar). Now I store some bills on it - how_much will get numbers with 2 digits after point. Why do you think that? Note that FLOAT is an approximate numeric type. The value you insert is stored in a binary format, and when you retrieve it, it may have a slightly different value from what you inserted, like you have observed. Read more here: http://www.mysql.com/doc/en/Problems_with_float.html > http://www.mysql.com/doc/en/Numeric_types.html > Aha, now I have SUM(ROUND(how_much,2)), this works so far. Does this mean I can not trust float, decimal etc. for financial purposes? Then I want to know how much money I spent each month. SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY (YEAR(when)*12+MONTH(when)) I don't understand this GROUP BY clause... the subject of this message indicates that this too was an issue? I would have expected "GROUP BY YEAR(when), MONTH(when)". The result is the same in my query. I expect to get numbers with not more than 2 digits after the point. Surprisingly I get ??.25978 when I expect to get ??.26 or some else strange numbers. See the ROUND() function: http://www.mysql.com/doc/en/Mathematical_functions.html#IDX1291 > Note the C library dependency of this function, it may behave differently on different computers. I would store the how_much as pfennnigs or cents or whatever and use an INTEGER type. -- Roger Thanks again, Adib. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by _date_
* [EMAIL PROTECTED] > Hello, > I have a table bill like > when (date), how_much (float), what (varchar). > > Now I store some bills on it - how_much will get numbers with 2 digits > after point. Why do you think that? Note that FLOAT is an approximate numeric type. The value you insert is stored in a binary format, and when you retrieve it, it may have a slightly different value from what you inserted, like you have observed. Read more here: http://www.mysql.com/doc/en/Problems_with_float.html > http://www.mysql.com/doc/en/Numeric_types.html > > Then I want to know how much money I spent each month. > SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY > (YEAR(when)*12+MONTH(when)) I don't understand this GROUP BY clause... the subject of this message indicates that this too was an issue? I would have expected "GROUP BY YEAR(when), MONTH(when)". > I expect to get numbers with not more than 2 digits after the point. > Surprisingly I get ??.25978 when I expect to get ??.26 or some else > strange numbers. See the ROUND() function: http://www.mysql.com/doc/en/Mathematical_functions.html#IDX1291 > Note the C library dependency of this function, it may behave differently on different computers. I would store the how_much as pfennnigs or cents or whatever and use an INTEGER type. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group by _date_
Hello, I have a table bill like when (date), how_much (float), what (varchar). Now I store some bills on it - how_much will get numbers with 2 digits after point. Then I want to know how much money I spent each month. SELECT SUM(how_much), YEAR(when), MONTH(when) FROM bill GROUP BY (YEAR(when)*12+MONTH(when)) I expect to get numbers with not more than 2 digits after the point. Surprisingly I get ??.25978 when I expect to get ??.26 or some else strange numbers. Where is my mistake ?? I already googel-t and browsed the archive, no success. Thanks, Adib. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
