I have a problem with joining 2 lines. I researched a lot, but found nothing that could solve my case.
In the query below, there are two SELECTs, which return two rows for each extension, as shown below. In the code, I need to merge these two lines to display on screen, but this merge in the code is causing a lot of 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 by 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)