replace 'spaces' from bigquery table

Question

I have a table

#standardSQL
with table as (
  select 'DE' country, 520 number union all
  select 'de' country, 480 number union all
  select ' DE' country, 500 number union all
  select ' DE   ' country, 500 number
)
select replace(UPPER(country), ' ', '') as country_shop, number from table
GROUP BY country, number

my current result looks like

row country_shop    number
1   DE              520
2   DE              480
3   DE              600
4   DE              400

I am trying to get the result as

row country_shop    number
1   DE              2000

Could someone please help me here? Thank you!

Answer 1

You are trying to group by the country_shop AND the number column but as we can see, this is not the result you want. You only want to group by the country_shop and sum all the numbers associated with that country.

Great, you already have functions that exist to remove all leading and trailing spaces called trim and another function called sum, you can then build a query like this:

select 
    upper(trim(country)) as country_shop,
    sum(number) as total_number
from table
group by country_shop

Please, read the following documentation with all the functions already provided by bigquery to find the ones that suit your needs.

Answer 2

Use below instead

#standardSQL
with table as (
  select 'DE' country, 520 number union all
  select 'de' country, 480 number union all
  select ' DE' country, 500 number union all
  select ' DE   ' country, 500 number
)
select replace(UPPER(country), ' ', '') as country_shop, sum(number) number from table
GROUP BY country_shop    

with output