sqljoingoogle-bigqueryunion-all

I'd like to have a bigquery query code to join and calculate 2 tables

I have 2 tables in Bigquery and I'd like to merge/join them and doing some calculation. Here are the tables :

Table A

ID Name Score
12-2112 John 23844
12-2310 Matthew 21881
13-6205 Matthew 16721
12-1710 Sonia 13344
12-1710 Sonia 8187

Table B

ID Name Games Score
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karate 1861
12-2310 Matthew Judo 2081
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score on Table A is the total score of games of Table B. However, not all games on Table B is identify. So, the final table should look like :

Combined

ID Name Games Score
12-2112 John null 6446
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 4156
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 838
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 586
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 707
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

I've tried all the joining statements possibles (I know) but the output is not as desired.

The best I did found was a union all (or distinct) with this query :

select
ID,
Name,
null as Games,
Score
from Table A
Union ALL
Select
ID,
Name,
Games,
Score
from Table A

and here is the output :

ID Name Games Score
12-2112 John null 23844
12-2112 John Soccer 10291
12-2112 John Soccer 2271
12-2112 John Soccer 3211
12-2112 John Soccer 1625
12-2310 Matthew null 21881
12-2310 Matthew Tennis 11551
12-2310 Matthew Volley 2232
12-2310 Matthew karaté 1861
12-2310 Matthew Judo 2081
13-6205 Matthew null 16721
13-6205 Matthew MMA 5281
13-6205 Matthew Racing 8681
13-6205 Matthew Volley 1921
12-1710 Sonia null 13344
12-1710 Sonia football 3324
12-1710 Sonia Volley 2716
12-1710 Sonia Judo 6718
18-1130 Sonia null 8187
18-1130 Sonia football 4281
18-1130 Sonia Tennis 3199

The score is not the total of table A - Sum (of Games) score of Table B as expected.

Could you please help me out ?

Thanks

I'm trying to joing query statement between table A and table B and have the score of table A to be the difference from the sum (table A) - sum (table B) for a null "games"

I've tried the following query :

select
ID,
Name,
null as Games,
Score
from Table A
Union ALL
Select
ID,
Name,
Games,
Score
from Table A
Solution

  • Consider below simple approach

    select id, name, games, 
  if(not games is null, score, 2 * score - sum(score) over(partition by id, name)) as score
from (
  select * from tableB union all
  select id, name, null, score from tableA
)

    if applied to sample data in your question - output is

    enter image description here