How to use aggregate function on column cotainining string values?

Question

I am using PostgreSQL, I've one column named Result in Job table, it contains values like 2/3, 1/3, 2/3.

For Example: Job table rows are:

job_id result
------ ------
1      2/3
2      1/3
3      2/3

I want to apply aggregate function on result column in such way that I can get result like: 5/9

But Result is of text type column and we cannot directly apply sum/avg/min/max etc. function on that column.

Can anybody suggest any other approach to achieve that result using query itself?

All suggestions are appreciated.

Thanks.

And what should happen if the result column has not small numbers but say 32/6734513, 467/12345679, 1/98765432011 ?? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 7, 2011 at 7:44

ypercubeᵀᴹ · Accepted Answer · 2011-07-07 08:52:41Z

3

SELECT SUM( dividend ) || '/' || SUM( divisor )
       AS FractionOfSums
     , AVG( dividend / divisor )        
       AS AverageOfFractions
FROM
  ( SELECT CAST(substr(result, 1, position('/' in result)-1 ) AS int)
           AS dividend
         , CAST(substr(result, position('/' in result)+1 ) AS int)
           AS divisor
    FROM rows
  ) AS division

edited Jul 7, 2011 at 8:52

answered Jul 7, 2011 at 7:49

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Martin Schapendonk Over a year ago

position('/', result) should be instr(result, '/') for Oracle

Aaron Digulla Over a year ago

Fixed the select. Not sure about the concatenation, though.

Denis de Bernardy Over a year ago

Concatenation should be || no?

ypercubeᵀᴹ Over a year ago

@Aaron: SUM(a) / SUM(b) does not always equal AVG(a/b). In fact, almost never, only in very special cases.

ypercubeᵀᴹ Over a year ago

@Aaron: I guess it's up to the OP to clarify what aggregate exactly he wants.

|

Johan · Accepted Answer · 2011-07-07 09:04:09Z

In addition to ypercube's excellent answer, if you want to simplify the fraction you'll need to find the greatest common divisor.

Here's pseudo code for a stored function that can generate this:

CREATE FUNCTION gcd(x int, y int) RETURNS int DETERMINISTIC
BEGIN
  DECLARE dividend int;
  DECLARE divisor int;
  DECLARE remainder int;

  SET dividend := GREATEST(x, y);
  SET remainder := LEAST(x, y);

  WHILE remainder != 0 DO
    SET divisor = remainder;
    SET remainder = MOD(dividend, divisor);
    SET dividend = divisor;
  END WHILE;

  RETURN divisor;
END

Now you can rewrite the query into:

SELECT (dividend/MyGCD) || '/' || (divisor/MyGCD) as FractionOfSums
  , AverageOfFractions 
FROM ( 
  SELECT 
    SUM( dividend ) as dividend
    , SUM( divisor ) AS divisor      
    , gcd(SUM( dividend ),SUM( divisor )) as MyGCD 
    , AVG( dividend / divisor ) AS AverageOfFractions 
  FROM ( 
    SELECT CAST(substr(result, 1, position('/', result)-1 ) AS int) AS dividend
        , CAST(substr(result, position('/', result)+1 ) AS int) AS divisor    
    FROM rows ) AS division 
) as Elements

Note that GCD is a very slow function.

Collectives™ on Stack Overflow

How to use aggregate function on column cotainining string values?

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related