Search code examples
sqlpostgresqldecimaldivide

How to calculate the ratio of this column with 2 rows

I am very new to SQL and am having difficulty figuring out hot to divide row1 (101) by row2 (576).

Image of table I am referring too

COUNT
101
576

I want the output to be a single value expressed to 2 decimal places.

Any tips?

Thanks for the help

Solution

  • For two rows, it's easy. If you have a big input table, and you want to divide the first row by the second, the third row by the fourth, etc, then you need an ordering column to save yourself.

    So, with a two-row table (remember, tables are never ordered), you just rely on the fact that you divide the smaller number by the bigger number.

    Here goes:

    WITH                                                                
-- your input ...
input(counter) AS ( -- count is reserved word, use another name ...
          SELECT 101
UNION ALL SELECT 576
)
-- cheat and just divide the smaller by the bigger
-- as "@Gordon Linoff" suggests
-- force a float division by adding a non-integer operand
-- and hard-cast it to DECIMAL(5,2)
SELECT 
  CAST(
     MIN(counter) * 1.00 / MAX(counter)
     AS DECIMAL(5,2)
  ) AS result
FROM input;
-- out  result 
-- out ----------
-- out      0.18

    If, however, you have many rows, and you always need to divide the first row by the second, the third row by the fourth, that is, each odd row in the order by the next even row in the order, then you need an ordering column.

    Is your problem just what you suggested, or is there more to it?