Divide two rows over Partition

Question

I have a table like this :

Id Sym sum_amount pair 
11  2    1000      1
11  3     500      1
22  4    200       2
22  4    50        2

And I would like to divide one value in a pair in the second in the same pair over partition by Id, Sym.

and get those results:

Id Sym sum_amount pair Div
11  2    1000      1    2
11  3    500       1   0.5
22  4    200       2    4
22  4    50        2   0.25

I think I will need something like this:

Ratio[???](Sum_Amount) Over (Partition by Id, Sym, pair)

Any way to do this? I know that can I average over the pair, sum etc but I don't know how can I do those ratios? are there a built in function for ratio?

Thanks.

Hmm, i think the penny dropped. What if there are more than two? Is the sample data wrong? I think `11 3` should be `11 2` to get same values as the first row. — Tim Schmelter, Feb 11 '16 at 13:04
@TimSchmelter . . . The column name is called "pair", so I think it is fair to assume that there are two of them. — Gordon Linoff, Feb 11 '16 at 13:06
@GordonLinoff: if it's always a pair it would be better to store them in one row in a separate column. Makes everything easier and is safer. — Tim Schmelter, Feb 11 '16 at 13:07

score 4 · Accepted Answer · answered Feb 11 '16 at 13:00

4

Hmmm. You need the other value for the division. One method is to get the min() and max() and choose the other one:

select id, sum, sum_amount, pair,
       (case when max(sum_amount) over (partition by pair) = sum_amount
             then sum_amount / min(sum_amount) over (partition by pair)
             else sum_amount / max(sum_amount) over (partition by pair)
        end) as div
from t;

answered Feb 11 '16 at 13:00

Gordon Linoff

1,242,037
58
646
786

Integer division prevents correct results and `sum` is `sym`. I think also it should be `Partition by Id, Sym, pair`. – Tim Schmelter Feb 11 '16 at 13:10
@Jordan . . . If `sum_amount` is an integer, then multiply by 1.0 or convert to an appropriate decimal type. – Gordon Linoff Feb 11 '16 at 14:08

score 1 · Answer 2 · answered Feb 11 '16 at 13:25

Similar to Gordon's approach but using a CTE, avoiding integer division and including all three columns as desired (i guess Sym=3 was a typo in your sample data):

WITH CTE AS
(
    select id, Sym, sum_amount, pair, 
           minAmount = min(sum_amount) over (partition by Id, Sym, pair),
           maxAmount = max(sum_amount) over (partition by Id, Sym, pair),
           rMin = 1.0 * sum_amount / min(sum_amount) over (partition by Id, Sym, pair),
           rMax = 1.0 * sum_amount / max(sum_amount) over (partition by Id, Sym, pair)
   FROM t
)
SELECT id, Sym, sum_amount, pair,
       Div = CASE WHEN sum_amount = maxAmount THEN rMin ELSE rMax END
FROM CTE;

sql-Fiddle

Divide two rows over Partition

2 Answers2