How to create/add a column in an SQL select query based on another column's values?

Question

I want to dynamically add another column hook_name, via an SQL select query, based on a condition.

For example if hook_type = 0, table hook_name should have a value of OFFER, and similarly for hook_type = 1, hook_name should show "ACCEPT".

Below is a screenshot of the result:

The select query is this:

select hook_type, 'hook name' as hook_name,
       count(*) as number_of_exchange_activities 
from `exchange` 
group by hook_type # hook_type 0 for OFFER, 1 for ACCEPT and 2 for offer EXPIRED;

Thanks in advance.

dnoeth · Accepted Answer · 2013-08-19 11:19:59Z

35

Use a Standard SQL CASE:

SELECT hook_type, 
   CASE hook_type
      WHEN 0 THEN 'OFFER'
      WHEN 1 THEN 'ACCEPT'
      WHEN 2 THEN 'EXPIRED'
   END AS hook_name,
   COUNT(*) AS number_of_exchange_activities 
FROM `exchange` 
GROUP BY hook_type

answered Aug 19, 2013 at 11:19

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2013-08-19 11:51:32Z

9

select case when hook_type = 0 then 'offer'
            when hook_type = 1 then 'accept'
            else 'expired'
       end as hook_t, 
      `hook name` as hook_name,
      count(*) as number_of_exchange_activities 
from `exchange` 
group by hook_t

BTW I think you want to escape the column name hook name. Then use backticks and not quotes.

edited Aug 19, 2013 at 11:51

answered Aug 19, 2013 at 11:17

juergen d

205k40 gold badges305 silver badges377 bronze badges

Collectives™ on Stack Overflow

How to create/add a column in an SQL select query based on another column's values?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related