Summarize the proportions stored in the table

Transact-SQL
1 
CREATE TABLE dbo.tab1
(
  idrow INT NOT null IDENTITY,
  group1 VARCHAR(10) NOT NULL,
  c1 NUMERIC(10,2) NOT NULL,
  c2 NUMERIC(10,2) NOT NULL
)
GO

INSERT INTO tab1 (
  group1,  --group
  c1,    --numerator
  c2    --denominator 
)
VALUES
('1', 1, 2),
('1', 2, 1.4),
('1', 3, 5.2),
('2', 1, 0.6),
('2', 4, 0.5),
('2', 3, 0.9)

to store the proportions, the numerator and denominator are stored in the table (there may be several for one group)
. For each group of such values, you need to get the total values in the same form: separately the numerator and separately the denominator

for example, for group 1, the correct answer will be as follows:

group1   c1      c2
1        36.48   14.56

because:
1/2 + 2/1.4 + 3/5.2 = according to the rules of school mathematics, we bring to a common denominator = 1 * 1.4 * 5.2 / (2 * 1.4 * 5.2) + 2 * 2 * 5.2 / 1.4 * 2 * 5.2 + 3 * 2 * 1.4 / 5.2 * 2 * 1.4 = (7.28 + 20.8 + 8.4) / 14.56 = 36.48 / 14.56
I hope I didn't make any mistakes in the calculation

or if, say, the result is values that can be shortened, such as:

group1   c1     c2
1        30     10

then it would be good (although not necessarily) to reduce them and get:

group1   c1     c2
1        3      1

something like the smallest common divisor (or the largest common multiple, I've forgotten since school days)

how to solve this in sql?
I can create ugly code and solve this, but maybe there is a better option?
I don't know how to multiply a variable number of values when there may be only 3 or many of them (it's easy to sum them up later).

that's how I tried to do it, but it's wrong, because you need to multiply other numbers, it doesn't get any further:
I would like to avoid recursive cte, but if not, then ok...

SELECT *, t1.c1 * t2.c1
FROM tab1 AS t1
JOIN tab1 AS t2 ON t1.group1 = t2.group1 AND t1.idrow <> t2.idrow
WHERE t1.group1 = '1'
2

Here's an algorithm/function for GCD:

Beyond that I don't have any suggestions. SQL is not really cut out for this kind of calculation, and I am not able to understand the example you posted and what order of operations you're following.

If you have to do a lot of this kind of thing, you might be better off using Python and one of its math libraries like NumPy. SQL Server 2017 and higher can also install machine learning elements that support Python, which would be useful if you had to do serious work with very large datasets. It's probably overkill for what you're describing here.

1 Like