I want to add a column to an existing table based on the condition of 3 columns in the table like the example below.
I have 3 columns :
- EXPIRED
- pending
- on_the_go
where each column had as value the ID number from another table of the corresponding value (expired value is 1)(pending value is 2)...
What I want is to add a column called status
that combine all these 3 columns into 1
I tried to use CASE WHEN
but that did not work as expected:
SELECT
EXPIRED, pending, on_the_go,
CASE EXPIRED
WHEN 1 THEN 1
END AS status_type,
CASE pending
WHEN 2 THEN 2
END AS status_type,
CASE on_the_go
WHEN 3 THEN 3
END AS status_type,
COUNT(*) AS number_of_exchange_activities
FROM
card
GROUP BY
EXPIRED, pending, on_the_go
EXPIRED | pending | on_the_go | status_type | status_type | status_type | number_of_exchange_activities |
---|---|---|---|---|---|---|
0 | 2 | 0 | NULL | 2 | NULL | 550 |
0 | 0 | 3 | NULL | NULL | 3 | 320 |
1 | 0 | 0 | 1 | NULL | NULL | 310 |
This is what I expected to get:
EXPIRED | pending | on_the_go | status_type | number_of_exchange_activities |
---|---|---|---|---|
0 | 2 | 0 | 2 | 550 |
0 | 0 | 3 | 3 | 320 |
1 | 0 | 0 | 1 | 310 |
CodePudding user response:
You can use the long form of case
that allows you to place full conditions in each when
clause:
SELECT expired, pending, on_the_go,
CASE WHEN expired = 1 THEN 1
WHEN pending = 2 THEN 2
WHEN on_the_go = 3 THEN 3
END AS status_type,
COUNT(*) AS number_of_exchange_activities
FROM card
GROUP BY expired, pending, on_the_go
CodePudding user response:
CASE
WHEN EXPIRED = 1 THEN 1
WHEN pending = 2 THEN 2
WHEN on_the_go = 3 THEN 3
ELSE 0
END As 'status_type'