unmounted unmounted - 1 year ago 79
SQL Question

SQL Absolute value across columns

I have a table that looks something like this:

word big expensive smart fast

dog 9 -10 -20 4
professor 2 4 40 -7
ferrari 7 50 0 48
alaska 10 0 1 0
gnat -3 0 0 0

The + and - values are associated with the word, so professor is smart and dog is not smart. Alaska is big, as a proportion of the total value associated with its entries, and the opposite is true of gnat.

Is there a good way to get the absolute value of the number farthest from zero, and some token whether absolute value =/= value? Relatedly, how might I calculate whether the results for a given value are proportionately large with respect to the other values? I would write something to format the output to the effect of: "dog: not smart, probably not expensive; professor smart; ferrari: fast, expensive; alaska: big; gnat: probably small." (The formatting is not a question, just an illustration, I am stuck on the underlying queries.)

Also, the rest of the program is python, so if there is any python solution with normal dbapi modules or a more abstract module, any help appreciated.

Answer Source

abs value fartherest from zero:

select max(abs(mycol)) from mytbl

will be zero if the value is negative:

select n+abs(mycol)
  from zzz
 where abs(mycol)=(select max(abs(mycol)) from mytbl);