mysql - Multiple select query with concatinated result displayed -

from table 'phone_call'

select * phone_call;  *************************** 181. row ***************************      id: 682 user_id: 24   start: 2013-09-03 17:59:18     end: 2013-09-03 17:59:20 ext_num: 2008 destination: 123473654172 call_id: 5eab044f19ed41d70661c3220f1b5c65 direction: created_at: 2013-09-03 17:59:18 updated_at: 2013-09-03 17:59:20  totals: 1 *************************** 182. row ***************************      id: 683 user_id: 22   start: 2013-09-03 17:59:18     end: 2013-09-03 17:59:20 ext_num: 2002 destination: 123456587458 call_id: 2eb1d9ea2e1937be30f1e93878eb3efe direction: o created_at: 2013-09-03 17:59:18 updated_at: 2013-09-03 17:59:20  totals: 1 182 rows in set (0.00 sec)  ...more 

from data above, need query returns:

 ext_num, username, calls_in, calls_out, avg_in, avg_out  

from data, in 1 on fly, struggling little.

i can individual parts so:

select avg(timediff(end, start)) avg_in phone_call user_id=24 ,  direction='i'; +---------------------------+ | avg_in                    | +---------------------------+ | 22.0810810811             | +---------------------------+ 1 row in set (0.00 sec)  select avg(timediff(end, start)) avg_out phone_call user_id=24 , direction='o'; +---------+ | avg_out | +---------+ |    null | +---------+ 1 row in set (0.00 sec) 

there no 'calls out' (direction='o') user (24).

and

select ext_num, sum(totals) `calls_in`, u.username phone_call pc  join user u on pc.user_id=u.id  user_id=24 , direction='i'; +---------+----------+----------+ | ext_num | calls_in | username | +---------+----------+----------+ |    2008 |       37 | esi      | +---------+----------+----------+ 1 row in set (0.00 sec) 

and

select sum(totals) `calls_out` phone_call user_id=24 , direction='o'; +-----------+ | calls_out | +-----------+ |      null | +-----------+ 1 row in set (0.00 sec) 

but how on earth join make 1 string or tupele?

edit: table structure:

describe phone_call; +-------------+--------------+------+-----+---------+----------------+ | field       | type         | null | key | default |          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      | no   | pri | null    | auto_increment | | user_id     | int(11)      | no   | mul | null    |                | | start       | datetime     | yes  |     | null    |                | | end         | datetime     | yes  |     | null    |                | | ext_num     | int(11)      | yes  |     | null    |                | | destination | varchar(45)  | yes  |     | null    |                | | call_id     | varchar(100) | yes  |     | null    |                | | direction   | varchar(1)   | yes  |     | null    |                | | created_at  | datetime     | yes  |     | null    |                | | updated_at  | datetime     | yes  |     | null    |                | | totals      | int(1)       | yes  |     | null    |                | +-------------+--------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec) 

edit: @mahmoud running:

select  u.ext_num, u.username, avg(case when pc.direction = 'i' timediff(pc.end, pc.start))  else 0 end)as avg_in, avg(case when pc.direction = 'o' timediff(pc.end, pc.start))  else 0 end)as avg_out, sum(case when pc.direction = 'i' totals else 0 end) `calls_in`,  sum(case when pc.direction = 'o' totals else 0 end) `calls_out` phone_call pc join user u on pc.user_id=u.id  u.user_id=24  group u.user_id, u.ext_num; error 1064 (42000): have error in sql syntax; check manual     corresponds mysql server version right syntax use near ')  else 0 end)as avg_in, avg(case when pc.direction = 'o' timediff(pc.end' @ line 4 

you need use case expression in 1 query, , use condition when pc.direction = 'i' , when pc.direction = 'o' each column:

select    u.ext_num,   u.username,   avg(case when pc.direction = 'i' timediff(pc.end, pc.start) end)as avg_in,   avg(case when pc.direction = 'o' timediff(pc.end, pc.start) end)as avg_out,   sum(case when pc.direction = 'i' totals else 0 end) `calls_in`,    sum(case when pc.direction = 'o' totals else 0 end) `calls_out` phone_call pc join user u on pc.user_id=u.id  u.user_id=24  group u.user_id, u.ext_num;