Use SQL to recode column value

Question

Questionnaire data (ordinal: 1,2,3,4 or 5) in Google BigQuery need to be fetched and aggregated using SQL syntax sum() or avg(). Before such column values aggregation can take place, some ordinal values (i.e., columns) need to be 'recoded', e.g.:

• 1 to become 5
• 2 to become 4
• 3 to remain 3
• 4 to become 2
• 5 to become 1

An example to explain the situation: the original data collection involved responding to:

• positively worded items (item1: "I love my work."), and
• reversed items (item2: "I hate my work.") Now both response values must be logically geared in the same 'direction' to then sum() into a new variable 'job satisfaction'; requiring item2 to be recoded.

How do I do that?

Answer 1

If those are the only values and you want to sum them, you can use:

sum(6 - item2)

Answer 2

If I understand correctly, you can use a case expression:

select sum(case item2
    when 1 then 5
    when 2 then 4
    when 3 then 3
    when 4 then 2
    when 5 then 1
end) sum_item2
from mytable