How to use aggregate function on column cotainining string values?

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 u开发者_Python百科sing query itself?

All suggestions are appreciated.

Thanks.

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

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.