Search code examples
sqlpostgresqlgroup-bycountsum

Divide the column by the sum grouped by another column

I have a table like this

source   |  destination   | frequency
-------------------------------------
   a     |       b        |     4
   a     |       c        |     2
   b     |       c        |     1
   b     |       a        |     3

and I would like to divide the frequency by the sum of frequency grouped by the source. Therefore, I am looking for a table like

source   |  destination   | frequency
-------------------------------------
   a     |       b        |     4/6
   a     |       c        |     2/6
   b     |       c        |     1/4
   b     |       a        |     3/4

Is there any way to do this in a single sql query?

Solution

  • You can use SUM() window function.

    If you want a numerical result:

    SELECT source, destination,
       frequency / SUM(frequency) OVER(PARTITION BY source)
FROM tablename

    Depending on your database, if it performs integer division between integers, you may need to multiply by 1.0:

    SELECT source, destination,
       1.0 * frequency / SUM(frequency) OVER(PARTITION BY source)
FROM tablename

    If you want the result as a string you can do it with concatenation:

    SELECT source, destination,
       CONCAT(frequency, '/', SUM(frequency) OVER(PARTITION BY source))
FROM tablename

    I used CONCAT(), but if your database does not support it use its concatenation operator and also you may need to convert the integer values to strings.