Divide the column by the sum grouped by another column

user_3681744 0
Viewed 861

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?

count group-by sum postgresql sql
3 Answers

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.

user_104988280

You can use sum over

select *, frequency /  Sum(frequency) over(partition by source)  
from table

If frequency is an int, multiply by 1.0 or cast/convert to get a decimal result.

user_153326500

For a single query, you can use join:

select t.source, t.destination, t.frequency/sum(t1.frequency) from testtable t join testtable t1 on t1.source = t.source group by t.source, t.destination
user_73267380
Related