CODEWITHSUNDEEP
sql
Federico
Federico

Reputation: 1

Counting many-to-many relationships with SQL

I have Table 1, with two columns as my input and need to get to table 2 with three columns as my output using SQL. The dataset has a large number of rows. How can I use SQL to obtain a count of all the E-A pairs in table 1 , normalized by their corresponding number of E's. Each E-A pair is one row. See Table 1 for the input and Table 2 for the desired output. Thank you very much for your help !

enter image description here

Upvotes: 0

Views: 132

Answers (1)

GMB
GMB

Reputation: 222512

You can use aggregation and window functions:

select
    e,
    a,
    count(*) occurences,
    1.0 * count(*) / sum(count(*)) over (partition by e) frequency
from mytable
group by e, a

This puts a and e in two different columns; you can use concat() or the-like if you want them in one column.

Upvotes: 1

Related Questions