{"id":255,"date":"2010-03-15T18:18:46","date_gmt":"2010-03-15T22:18:46","guid":{"rendered":"http:\/\/www.sofastatistics.com\/blog\/?p=255"},"modified":"2010-03-15T18:22:53","modified_gmt":"2010-03-15T22:22:53","slug":"sql-and-integer-division-why-52-usually-equals-2","status":"publish","type":"post","link":"http:\/\/www.sofastatistics.com\/blog\/sql-and-integer-division-why-52-usually-equals-2\/","title":{"rendered":"SQL &#038; integer division (why 5\/2 usually equals 2!)"},"content":{"rendered":"<p>I came across integer division in Python 2.x.  If you divide one integer by another you get an integer result.  So 5\/2 = 2 instead of 2.5.  You get floor division, not true division (<a href=\"http:\/\/www.python.org\/dev\/peps\/pep-0238\/\">Python &#8211; Changing the Division Operator<\/a>).  In Python 3, true division is the default (thank goodness) but in Python 2.x you need to make one of the numbers a float to get a float returned.  So 5.0\/2 = 2.5.  I was bitten by this early on and know the standard way of handling it.<\/p>\n<p>What I didn&#8217;t know was that integer division was the norm in SQL database SELECT statements.  I had mainly been using MySQL and MySQL was pretty unique as it turned out:<\/p>\n<blockquote><p>MySQL by default does floating point division, even if both operators are of type INTEGER, so the above [1\/2] would return 0.5 in MySQL. All of the other database engines tested do integer division, and return an integer result. (<a href=\"http:\/\/www.sqlite.org\/sqllogictest\/wiki?name=Differences+Between+Engines\">SQLite &#8211; Differences Between Engines<\/a>).<\/p><\/blockquote>\n<p>Anyway, in SOFA Statistics, row and column percentages were affected by this behaviour and always returned x.0 %.  There was never anything other than zero after the decimal point.  The fix was very simple.  Instead of SELECT &#8230; 100*(num\/denom) the relevant code is SELECT &#8230; 100.0*(num\/denom).  The 100 is now a float for those who missed that small but significant difference.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I came across integer division in Python 2.x. If you divide one integer by another you get an integer result. So 5\/2 = 2 instead of 2.5. You get floor division, not true division (Python &#8211; Changing the Division Operator). In Python 3, true division is the default (thank goodness) but in Python 2.x you [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5],"tags":[],"class_list":["post-255","post","type-post","status-publish","format-standard","hentry","category-general","category-python"],"_links":{"self":[{"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/posts\/255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/comments?post=255"}],"version-history":[{"count":6,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/posts\/255\/revisions"}],"predecessor-version":[{"id":261,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/posts\/255\/revisions\/261"}],"wp:attachment":[{"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/media?parent=255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/categories?post=255"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sofastatistics.com\/blog\/wp-json\/wp\/v2\/tags?post=255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}