# Summarize the proportions stored in the table

• `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)GOINSERT 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.c1FROM tab1 AS t1JOIN tab1 AS t2 ON t1.group1 = t2.group1 AND t1.idrow <> t2.idrowWHERE t1.group1 = '1'`

• This topic was modified 3 weeks, 4 days ago by  us26.
• This took a bit of head scratching.  I managed to solve it, assuming that there are a max of 3 parts per group (as per your example).

I then added a potential 2 extra per group, and commented the code out, so that you can see how to expand it if you ever get more parts per group.

`DECLARE @tab1 table (    idrow  int            NOT NULL IDENTITY  , group1 varchar(10)    NOT NULL  , c1     numeric(10, 2) NOT NULL  , c2     numeric(10, 2) NOT NULL);INSERT INTO  @tab1(  group1  --group, c1      --numerator, c2      --denominator )VALUES ( '1', 1, 2.0 ), ( '1', 2, 1.4 ), ( '1', 3, 5.2 )     , ( '2', 1, 0.6 ), ( '2', 4, 0.5 ), ( '2', 3, 0.9 );WITH cteBase AS (  SELECT t1.group1       , idrow1 = t1.idrow       , t1.c1       , idrow2 = t2.idrow       , t2.c2       , rn = ROW_NUMBER() OVER (PARTITION BY t1.group1, t1.idrow ORDER BY t2.idrow)  FROM       @tab1 AS t1  INNER JOIN @tab1 AS t2 ON t1.group1 = t2.group1), cteGroup AS (  SELECT b.idrow1       , b.group1       , num  = CASE WHEN b.idrow1  = b.idrow2              THEN b.c1 ELSE NULL END       , den1 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 1 THEN b.c2 ELSE NULL END       , den2 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 2 THEN b.c2 ELSE NULL END       , den3 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 3 THEN b.c2 ELSE NULL END--       , den4 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 4 THEN b.c2 ELSE NULL END--       , den5 = CASE WHEN b.idrow1 != b.idrow2 AND b.rn = 5 THEN b.c2 ELSE NULL END  FROM cteBase AS b), cteNumerator AS (  SELECT g.group1       , g.idrow1       , num  = ISNULL(CAST(MAX(g.num ) AS numeric(10, 2)), 1.00)              * ISNULL(CAST(MAX(g.den1) AS numeric(10, 2)), 1.00)              * ISNULL(CAST(MAX(g.den2) AS numeric(10, 2)), 1.00)              * ISNULL(CAST(MAX(g.den3) AS numeric(10, 2)), 1.00)--              * ISNULL(CAST(MAX(g.den4) AS numeric(10, 2)), 1.00)--              * ISNULL(CAST(MAX(g.den5) AS numeric(10, 2)), 1.00)       , den1 = CAST(MAX(g.den1) AS numeric(10, 2))       , den2 = CAST(MAX(g.den2) AS numeric(10, 2))       , den3 = CAST(MAX(g.den3) AS numeric(10, 2))--       , den4 = CAST(MAX(g.den4) AS numeric(10, 2))--       , den5 = CAST(MAX(g.den5) AS numeric(10, 2))  FROM cteGroup AS g  GROUP BY g.group1, g.idrow1)SELECT n.group1     , num  = SUM(n.num)     , den  = ISNULL(MAX(n.den1), 1.00)            * ISNULL(MAX(n.den2), 1.00)            * ISNULL(MAX(n.den3), 1.00)--            * ISNULL(MAX(n.den4), 1.00)--            * ISNULL(MAX(n.den5), 1.00)FROM cteNumerator AS nGROUP BY n.group1ORDER BY n.group1;`
• Thank you for help!

Now I see clearly that recursive cte should be used here to support unknown number of parts in each group without changing the code.

• us26 wrote:

according to the rules of school mathematics, we bring to a common denominator

How to calculate the product of all of the denominators for each group?  Why doesn't SQL Server have a PRODUCT function?  Seems like an oversight.  It would be nice to claim credit for how to do this but ChatGPT-4o suggested to use logarithms/exponents and it seemed like a good idea.  According to the rules of school mathematics logarithms convert multiplication into addition and exponents do the opposite.    Back in the day conversion tables were kept in books.  Once the common denominators are calculated (in the first cte) the TotalNumerator's and TotalDenominator's are summed in the 2nd cte.  The outer query OUTER APPLY's any greatest common denominator (gcd) using dbo.fnTally to count up to the lower of the totaled (c1, c2) tuple values and checking the modulo's.  Here's a link to an article with the code for dbo.fnTally by Jeff Moden

`drop TABLE if exists #tab1;goCREATE TABLE #tab1(  idrow INT NOT null IDENTITY,  group1 VARCHAR(10) NOT NULL,  c1 NUMERIC(10,2) NOT NULL,  c2 NUMERIC(10,2) NOT NULL);INSERT INTO #tab1 (group1, c1, c2) VALUES('1', 1, 2),   ('1', 2, 1.4),('1', 3, 5.2), ('2', 1, 0.6),('2', 4, 0.5), ('2', 3, 0.9);-- Step 1: Calculate the product of all denominators for each groupWITH DenominatorProduct AS (    SELECT group1,           EXP(SUM(LOG(c2))) AS TotalDenominator    FROM #tab1    GROUP BY group1),-- Step 2: Calculate the adjusted numerators and sum them up for each groupSummedValues AS (    SELECT t.group1,           SUM(t.c1 * dp.TotalDenominator / t.c2) AS TotalNumerator,           dp.TotalDenominator AS TotalDenominator    FROM #tab1 t         JOIN DenominatorProduct dp ON t.group1 = dp.group1    GROUP BY t.group1, dp.TotalDenominator)-- Step 3: Reduce the fraction to its simplest form using OUTER APPLY and fnTallySELECT sv.group1,       sv.TotalNumerator,       sv.TotalDenominator,       sv.TotalNumerator / gcd.GCDValue AS ReducedNumerator,       sv.TotalDenominator / gcd.GCDValue AS ReducedDenominatorFROM SummedValues sv     OUTER APPLY (SELECT MAX(N) AS GCDValue                  FROM dbo.fnTally(1, CASE WHEN sv.TotalNumerator < sv.TotalDenominator                                            THEN sv.TotalNumerator                                            ELSE sv.TotalDenominator END)                  WHERE CAST(sv.TotalNumerator AS BIGINT) % N = 0                         AND CAST(sv.TotalDenominator AS BIGINT) % N = 0) gcdorder by sv.group1;`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Thank you but I didn't find the code of fnTally on the link.

I also didn't understand how to use log/exp functions to solve this task.

• This reply was modified 3 weeks, 3 days ago by  us26.
• us26 wrote:

Thank you but I didn't find the code of fnTally on the link. I also didn't understand how to use log/exp functions to solve this task.

Regarding the use of log and exponent.

`EXP(SUM(LOG(c2))) AS TotalDenominator`

The inner function LOG(c2) converts c2 to it's log representation.  As the innermost function it converts c2 for each row.  SUM sums the converted LOG(c2)'s across the rows of the GROUP BY column(s).  Then EXP converts the summed logs into the product of all of the c2's.  I asked ChatGPT-4o to create an example script in SQL Server and here's what it came up with

`-- Properties of Logarithms-- 1. Logarithm of a Product:--    The logarithm of a product is the sum of the logarithms of the factors:--    log(a * b) = log(a) + log(b)---- 2. Exponentiation:--    The exponential function is the inverse of the logarithm function:--    exp(log(x)) = x-- Example with Two Numbers-- Let's say we have two numbers, 4 and 5, and we want to calculate their product using logarithms.-- Step 1: Convert to Logarithms-- Calculate the natural logarithm of each number:-- log(4) ≈ 1.386-- log(5) ≈ 1.609-- Step 2: Sum the Logarithms-- Add the logarithms together:-- log(4) + log(5) ≈ 1.386 + 1.609 = 2.995-- Step 3: Convert Back Using Exponentiation-- Use the exponential function to convert the sum back to the product:-- exp(2.995) ≈ 20-- SQL Example-- Create a temporary table with two numbersDROP TABLE IF EXISTS #temp;GOCREATE TABLE #temp (    Value NUMERIC(10, 2));-- Insert the two numbers into the temporary tableINSERT INTO #temp (Value) VALUES (4), (5);-- Calculate the product using the EXP(SUM(LOG(Value))) trickSELECT EXP(SUM(LOG(Value))) AS ProductFROM #temp;`

To get dbo.fnTally maybe there's a link at the end of the article?  Here's the one I'm using

`CREATE FUNCTION [dbo].[fnTally]/**********************************************************************************************************************    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally**********************************************************************************************************************/        (@ZeroOrOne BIT, @MaxN BIGINT)RETURNS TABLE WITH SCHEMABINDING AS  RETURN WITH  H2(N) AS ( SELECT 1                FROM (VALUES                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    )V(N))            --16^2 or 256 rows, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL            SELECT TOP(@MaxN)                   N = ROW_NUMBER() OVER (ORDER BY N)              FROM H8;`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Steve Collins wrote:

us26 wrote:

according to the rules of school mathematics, we bring to a common denominator

How to calculate the product of all of the denominators for each group?  Why doesn't SQL Server have a PRODUCT function?  Seems like an oversight.  It would be nice to claim credit for how to do this but ChatGPT-4o suggested to use logarithms/exponents and it seemed like a good idea.  According to the rules of school mathematics logarithms convert multiplication into addition and exponents do the opposite.    Back in the day conversion tables were kept in books.  Once the common denominators are calculated (in the first cte) the TotalNumerator's and TotalDenominator's are summed in the 2nd cte.  The outer query OUTER APPLY's any greatest common denominator (gcd) using dbo.fnTally to count up to the lower of the totaled (c1, c2) tuple values and checking the modulo's.

Man, I hated those darn log books.  Who would have though that 40 years later, they'd come back to haunt me.

• Ha, a haunting could be a good thing 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply