CODEWITHSUNDEEP
mysql
user2680315
user2680315

Reputation: 113

Create 2 columns based on value in existing column

I have the following table. I would like to add 2 new columns with a select query that will show the total based on the flag type.

Table:

tt        | company     | count  | flag
  --------------------------------------------
  123     | adeco       | 5      | 1
  123     | mic         | 4      | 2 
  333     | manpower    | 88     | 2
  444     | linar       | 2      | 2
  555     | dlank       | 3      | 1

Desired:

tt        | company     | total  | flag  | total_flag1 | total_flag2
  -------------------------------------------------------------------
  123     | adeco       | 5      | 1     | 5           | 0
  123     | mic         | 4      | 2     | 0           | 4 
  333     | manpower    | 88     | 2     | 0           | 88
  444     | linar       | 2      | 2     | 0           | 2
  555     | dlank       | 3      | 1     | 3           | 0

Upvotes: 0

Views: 624

Answers (2)

Blank
Blank

Reputation: 12378

By your desired result, you should use case when or if syntax to to this:

select 
    yourtable.*,
    case when flag = 1 then `count` else 0 end as total_flag1,
    case when flag = 2 then `count` else 0 end as total_flag2
from yourtable

Or

select 
    yourtable.*,
    if(flag = 1, `count`, 0) as total_flag1,
    if(flag = 2, `count`, 0) as total_flag2
from yourtable

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you can do what you want using correlated subqueries or join:

select t.*, tsum.total_flag1, tsum.total_flag2
from t join
     (select t.tt,
             sum(case when flag = 1 then total else 0 end) as total_flag1,
             sum(case when flag = 2 then total else 0 end) as total_flag2
      from t
      group by t.tt
     ) tsum
     on t.tt = tsum.tt;

Upvotes: 0

Related Questions