CODEWITHSUNDEEP
mysqlselect
Vincent
Vincent

Reputation: 852

search query if condition column possible?

I have this table that which has a duration of seconds recorded based on the call_type. now i want to display the incoming and outgoing of call type and it's duration in a single row.

call TABLE

|ID | originating call |   terminating call |  call type    | duration_seconds
| 1 | 123              |       123          |  incoming     |     60
| 2 | 123              |       123          |  outgoing     |     120 
| 3 | 123              |       321          |  incoming     |     210
| 4 | 123              |       321          |  incoming     |     140

and the result will be

|ID | originating call |   terminating call |  incoming  |   duration | outgoing   | duration 
| 1 | 123              |       123          |     1      |     60     |      1     |   120
| 2 | 123              |       321          |     2      |     350    |      0     |   0

this is my query so far. 

select @id := @id + 1 as id,
   originating, terminating,
   sum(calltype = 'incoming') as incoming,
   sum(calltype = 'outgoing') as outgoing,
from calltable ct cross join
 (select @id := 0) const
group by originating, terminating;


 originating call |   terminating call |  incoming  | outgoing
      123         |       123          |     1      |    1
      123         |       321          |     2      |    0

Upvotes: 0

Views: 64

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

just simply add another 2 columns based on calltype just like you did for the incoming and outgoing except instead of counting 1, use duration_seconds

select row_number() over (order by (select NULL)) as id,
   originating, terminating,
   sum(case when calltype = 'incoming' then 1 else 0 end) as incoming,
   sum(case when calltype = 'incoming' then duration_seconds else 0 end) as in_duration,
   sum(case when calltype = 'outgoing' then 1 else 0 end) as outgoing,
   sum(case when calltype = 'outgoing' then duration_seconds else 0 end) as out_duration
from calltable ct
group by originating, terminating;

here's mysql according to your updated question.

select @id := @id + 1 as id,
   originating, terminating,
   sum(calltype = 'incoming') as incoming,
   sum(if(calltype = 'incoming',duration_seconds,0)) as in_duration,
   sum(calltype = 'outgoing') as outgoing,
   sum(if(calltype = 'outgoing',duration_seconds,0)) as out_duration
from calltable ct cross join
 (select @id := 0) const
group by originating, terminating;

Upvotes: 1

Related Questions