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?

Answer

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.