A have a table in postgresql like this:

And I wish to do do a query that get all pairs and its relative percentage of elem2 that matches elem 1. The output would be like this:

CodePudding user response：

You need to independently count elem1 and (elem1,elem2). You can get this with a CTE for each. Then JOIN them on elem1 and compute the percentage of elem1 to elem2. (see demo)

with e1(elem1, c1) as   
       ( select elem1, count(*) 
           from test 
          group by elem1 
       )  
   , e2(elem1,elem2,c2) as  
       ( select elem1, elem2, count(*) 
           from test 
          group by elem1, elem2
       ) 
select e2.elem1, e2.elem2, round((1.0 * c2)/c1,2) percentage
  from e1 
  join e2 on (e2.elem1 = e1.elem1)
 order by e2.elem1, e2.elem2;