{"id":335,"date":"2012-01-09T18:32:18","date_gmt":"2012-01-09T17:32:18","guid":{"rendered":"http:\/\/jaran.de\/goodbits\/?p=335"},"modified":"2012-01-09T18:32:18","modified_gmt":"2012-01-09T17:32:18","slug":"mysql-from-differences-to-gains-and-losses","status":"publish","type":"post","link":"http:\/\/jaran.de\/goodbits\/2012\/01\/09\/mysql-from-differences-to-gains-and-losses\/","title":{"rendered":"MySQL: From Differences to Gains and Losses"},"content":{"rendered":"<p>The problem with <a title=\"Rankings in MySQL \u2013 global ranks and local ranks within subsets\" href=\"http:\/\/jaran.de\/goodbits\/2012\/01\/08\/rankings-in-mysql-global-ranks-and-local-ranks-within-subsets\/\">blogging solutions specifically for one person<\/a> is that he comes back and asks for more \ud83d\ude09 Luckily this time it&#8217;s easier!<\/p>\n<p><strong>What we got:<\/strong> a simple table with one column whose values describe a difference of some kind. That is we got one column called <em>difference<\/em>, that has negative or positive (or 0) numbers.<br \/>\n<strong>What we want:<\/strong> we&#8217;re looking for the most simple and performant way to update two other columns from that <em>difference<\/em> one: if the difference was positive, this value is to be updated into a <em>gain<\/em> column. If it is negative, then the positive value (the absolute value) is to be updated into the <em>loss<\/em> column.<\/p>\n<p><!--more-->So an initial table (named <em>differences<\/em>) might look like this:<\/p>\n<table>\n<tbody>\n<tr>\n<th>difference<\/th>\n<th>gain<\/th>\n<th>loss<\/th>\n<\/tr>\n<tr>\n<td>-13<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>27<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>0<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>-3<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>16<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The update query is quite simple and uses the conditional if-function:<\/p>\n<pre lang=\"MySQL\">\r\nUPDATE differences\r\n   SET gain = IF(difference > 0, difference, 0),\r\n       loss = IF(difference < 0, -difference, 0)\r\n<\/pre>\n<p>And the result:<\/p>\n<table>\n<tbody>\n<tr>\n<th>difference<\/th>\n<th>gain<\/th>\n<th>loss<\/th>\n<\/tr>\n<tr>\n<td>-13<\/td>\n<td>0<\/td>\n<td>13<\/td>\n<\/tr>\n<tr>\n<td>27<\/td>\n<td>27<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>2<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>0<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr>\n<td>-3<\/td>\n<td>0<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>16<\/td>\n<td>16<\/td>\n<td>0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>I expect the first commenter to headslap himself \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The problem with blogging solutions specifically for one person is that he comes back and asks for more \ud83d\ude09 Luckily this time it&#8217;s easier! What we got: a simple table with one column whose values describe a difference of some &hellip;<\/p>\n<p class=\"read-more\"><a href=\"http:\/\/jaran.de\/goodbits\/2012\/01\/09\/mysql-from-differences-to-gains-and-losses\/\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[5,28,9],"tags":[],"_links":{"self":[{"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/posts\/335"}],"collection":[{"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/comments?post=335"}],"version-history":[{"count":4,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/posts\/335\/revisions"}],"predecessor-version":[{"id":339,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/posts\/335\/revisions\/339"}],"wp:attachment":[{"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/media?parent=335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/categories?post=335"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jaran.de\/goodbits\/wp-json\/wp\/v2\/tags?post=335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}