1
I’m having a problem joining 2 lines. I did a lot of research, but I couldn’t find anything that could solve my case.
In the query below, there are two SELECT, which return two lines for each extension, as shown below. In the code, I need to join these two lines to display on screen, but this junction in the code is causing several problems.
Does anyone have any idea how I can return this result in just one line? For example:
|2016-04-15|2200|Bruna Farias da Silva Neves|1|454|18|470|
Note: I need to group by date and extension.
( SELECT date(cdr.calldate) as data,
cdr.dst as ramal,
users.name as nome,
count(date(cdr.calldate)) as qtde_entrante,
sum(cdr.duration) as tempo_entrante,
NULL as qtde_sainte,
NULL as tempo_sainte
FROM (`asteriskcdrdb`.`cdr`)
JOIN `asterisk`.`users` ON `users`.`extension` = `cdr`.`dst`
WHERE cdr.calldate >= '2016-04-15 00:00:00'
and cdr.calldate <='2016-04-15 23:59:59'
AND cdr.dst LIKE '220%'
GROUP BY date(cdr.calldate), cdr.dst)
UNION ALL
( SELECT date(cdr.calldate) as data,
cdr.src as ramal,
users.name as nome,
NULL as qtde_entrante,
NULL as tempo_entrante,
count(date(cdr.calldate)) as qtde_sainte,
sum(cdr.duration) as tempo_sainte
FROM (`asteriskcdrdb`.`cdr`)
JOIN `asterisk`.`users` ON `users`.`extension` = `cdr`.`src`
WHERE cdr.calldate >= '2016-04-15 00:00:00'
and cdr.calldate <='2016-04-15 23:59:59'
AND cdr.src LIKE '220%'
AND cdr.dst not in ('s')
AND cdr.dst not like '*2%'
AND (cdr.dst < 900 OR cdr.dst > 999)
GROUP BY date(cdr.calldate), cdr.src)
ORDER BY date(data) asc, ramal asc)
look for group_concat, but be careful that it might slow you down a little, but it might help you
– Marcelo Diniz
Hello Marcelo. I found this function, but I could not apply it in my example :(
– Diego Ramon
Hello, I couldn’t figure out what you want. Can you put an example http:/sqlfiddle.com/. To help.
– Tiago Gomes