Mapping two columns into one column in Athena

I have data in the Athena something like this:

| shop_id  | date        | provider | provider_new |  sales
| 123      | 2020-06-01  |     X    |              |   100
| 124      | 2020-07-01  |     Y    |              |   200
| 125      | 2020-08-01  |          |      Z       |   300
| 126      | 2020-09-01  |          |      T       |   400

However, the table owner changed the provider to provider_new. Thus, after 2020-08-01 the provider returns to NULL. Here is my query:

SELECT date,
         shop_id,
             
    CASE
    WHEN provider = 'X' THEN
    'X'
    WHEN provider = 'Y' THEN
    'Y'
    WHEN provider LIKE 'Z%' THEN
    'Z'
    WHEN provider LIKE 'T%' THEN
    'T'
    ELSE 'M'
    END AS provider_group,
         SUM(sales) AS sales
FROM X
GROUP BY  1, 2

How can I map these two columns to one?

Thanks in advance.

Answer

Do you want coalesce()?

coalesce(provider, provider_new)

coalesce() returns the first non-null value of the arguments it is given.

Advertisement

Answer