I have a table t1

a b 1 4 2 2 3 9 2 1 1 6 3 7

I need the output

a result 1 4*6 2 2*1 3 9*7

I have been able to do it in a far from efficient way and would like a more satisfactory way. Thank you.

If you don’t want a floating point result, and you can have more than 2 `b`

values for each `a`

value, you can use a recursive CTE to generate the results:

WITH RECURSIVE t1r AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY a) AS rn FROM t1 ), maxrows AS ( SELECT a, MAX(rn) AS maxrow FROM t1r GROUP BY a ), mul AS ( SELECT a, b AS result, rn, rn AS maxrow FROM t1r WHERE rn = 1 UNION ALL SELECT mul.a, result * b, t1r.rn, t1r.rn FROM mul JOIN t1r ON t1r.a = mul.a AND t1r.rn = mul.rn + 1 ) SELECT mul.a, mul.result FROM mul JOIN maxrows mr ON mr.a = mul.a AND mr.maxrow = mul.rn ORDER BY mul.a

Output:

a result 1 24 2 2 3 63

Note that this could be simplified if there was a column by which you could order the rows, that would remove the need to have the `maxrows`

CTE.