Group by and sum depending on cases in Google Big Query

Question

The data looks like-

A_value      B_value    C_value     Type
   1          null        null       A
   2          null        null       A
  null         3          null       B
  null         4          null       B
  null        null         5         C
  null        null         6         C

When Type is 'A' I want to sum the 'A_value' and store in a different column called 'Type_value', when Type is 'B' I want to sum the 'B_value' and store in the column 'Type_value' and do similar for 'C'

Expected results-

Type_value      Type
    3            A
    7            B
    11           C

How to achieve this result?

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT SUM(CASE Type
    WHEN 'A' THEN A_value
    WHEN 'B' THEN B_value
    WHEN 'C' THEN C_value
    ELSE 0
  END) AS Type_value, Type 
FROM `project.dataset.table`
GROUP BY Type   

If to apply to sample data in your question - result is

Row Type_value  Type     
1   3           A    
2   7           B    
3   11          C    

Another potential option is to reuse the fact that your data has pattern of having value only in respective columns. So if it is true - you can use below version

#standardSQL
SELECT SUM(IFNULL(A_value, 0) + IFNULL(B_value, 0) + IFNULL(C_value, 0)) AS Type_value, Type 
FROM `project.dataset.table`
GROUP BY Type   

with same result obviously