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?

## Advertisement

## 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.