Nate Miller Nate Miller -4 years ago 94
SQL Question

Sum/consolidate/combine reversed pairs in Big Query

I have a table which is something like this with counts of paired relationships with the pair often in reversed order.

country1 country2 count
CHN KOR 65
TWN KOR 32
KOR CHN 43


Here I have CHN - KOR and KOR - CHN. If I have already determined that these are distinct counts then these just represent two ways of describing a relationship and I would like to sum the counts of the pair so the final result is

country1 country2 count
CHN KOR 108
TWN KOR 32


I'm using Big Query. Does anyone know of a way of consolidating reversed pairs in SQL? Note: These are not duplicates, so this is not a question of removing duplicates, but combining reversed pairs

Answer Source

Here is one method:

select country1, country2, sum(count)
from ((select country1, country2, count
       from t
       where country1 <= country2
      ) union all
      (select country2, country1, count
       from t
       where country1 > country2
      )
     ) cc
group by country1, country2;

This will work for both the legacy and standard interface. For the standard, BigQuery supports greatest() and least() on strings:

select least(country1, country2), greatest(country1, country2), sum(count)
from ((select country1, country2, count
       from t
       where country1 <= country2
      ) union all
      (select country2, country1, count
       from t
       where country1 > country2
      )
     ) cc
group by 1, 2;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download