Сложный многопользовательский запрос MySQL

Question

Моя проблема в том, что я даже не знаю, возможен ли такой запрос. Я постараюсь объяснить:

У меня есть две таблицы, касающиеся телефонных звонков, "Звонки" и "Failed_Calls".

Важными столбцами в обеих таблицах являются "Пункт назначения" и "Маршрут", но есть еще много таких, как Id_call и start_date, named_number и так далее. Я опущу фильтры относительно периодов времени, чтобы упростить. В "Вызовах" можно указать адресатов, которых нет в "Failed_Calls", и наоборот.

Я хочу получить для каждой возможной пары "Назначение" и "Маршрут" количество вызовов и число неудачных вызовов, например, следующий код:

select c.Destination, c.Route, count(c.id_call) as Correct, null as Failed 
from Calls c
where c.Destination like ('Algeria%')
group by c.Destination, c.Route
union all
select f.Destination, f.Route, null, count(f.id_failed_call) as Failed
from Failed_Calls f
where f.Destination like ('Algeria%')
group by f.Destination, f.Route

Это показывает:

Destination    Route   Correct   Failed
Algeria        9       1         NULL
Algeria Mobile 9       4         NULL
Algeria Mobile 9       NULL      2

... это правильно, но мне нужно показать данные по двум последним строкам в одной строке, т. е. количество как правильных, так и неудачных вызовов для этого пункта назначения и маршрута.

Я пробовал с join, left join и без join, но я всегда получаю неправильный счет, что-то вроде результата вызовов и неудачных вызовов для каждой пары. Мой лучший выстрел до сих пор:

select c.Destination, c.Route, count(distinct(c.id_call)) as Correct,
 count(distinct(f.id_failed_call)) as Failed
from Calls c, Failed_Calls f
where c.Destination like 'Algeria%'
 and f.Destination like 'Algeria%'
group by c.Destination, c.Route

... который возвращает следующее:

Destination     Route     Correct    Failed
Algeria         9         1          2
Algeria Mobile  9         4          2

Столбец "Правильно" в порядке, но в столбце "Неудачно" отображается сумма неудачных вызовов для всех возвращенных адресатов в каждой строке (я проверил это с большим количеством адресатов в запросе).

Если такой запрос возможен, я бы хотел, чтобы кто-то помог мне с этим.

Answer 1

Я не пробовал это в этом случае, но я использовал такой запрос прежде, чтобы суммировать данные из UNIONs.

select Destination, route, sum(Correct), sum(Failed ) from (
    select c.Destination, c.Route, count(c.id_call) as Correct, 0 as Failed 
    from Calls c
    where c.Destination like ('Algeria%')
    group by c.Destination, c.Route
    union all
    select f.Destination, f.Route, 0 as Correct, count(f.id_failed_call) as Failed
    from Failed_Calls f
    where f.Destination like ('Algeria%')
    group by f.Destination, f.Route) as temp_table
group by Destination, route;

Answer 2

Попробуйте что-то вроде:

select c.Destination, c.Route, SUM(c.id_call) as Correct,
SUM(case when f.id_failed_call is NULL then 0 else 1 end)) as Failed
from Calls c, Failed_Calls f
where c.Destination like 'Algeria%'
and f.Destination like 'Algeria%'
group by c.Destination, c.Route

Answer 3

Вы должны быть в состоянии обернуть ваш рабочий запрос во внешний выбор и сгруппировать строки там;

SELECT Destination, Route,
       COALESCE(SUM(Correct), 0) Correct, COALESCE(SUM(Failed), 0) Failed
FROM (
  SELECT c.Destination, c.Route, COUNT(c.id_call) as Correct, NULL AS Failed 
  FROM Calls c
  WHERE c.Destination like ('Algeria%')
  GROUP BY c.Destination, c.Route
  UNION ALL
  SELECT f.Destination, f.Route, NULL, COUNT(f.id_failed_call) AS Failed
  FROM Failed_Calls f
  WHERE f.Destination LIKE ('Algeria%')
  GROUP BY f.Destination, f.Route
)
GROUP BY Destination, Route;

Answer 4

Пытаться

SELECT dr.DESTINATION,
       dr.ROUTE,
       IFNULL(c.CORRECT_COUNT, 0) AS CORRECT_COUNT,
       IFNULL(f.FAILED_COUNT, 0) AS FAILED_COUNT
  FROM (SELECT DISTINCT DESTINATION, ROUTE
          FROM CALLS
        UNION DISTINCT
        SELECT DISTINCT DESTINATION, ROUTE
          FROM FAILED_CALLS) dr
  LEFT OUTER JOIN (SELECT DESTINATION,
                          ROUTE,
                          COUNT(DISTINCT ID_CALL) AS CORRECT_COUNT
                     FROM CALLS
                     GROUP BY DESTINATION, ROUTE) c
    ON (c.DESTINATION = dr.DESTINATION AND
        c.ROUTE = dr.ROUTE)
  LEFT OUTER JOIN (SELECT DESTINATION,
                          ROUTE,
                          COUNT(DISTINCT ID_CALL) AS FAILED_COUNT
                     FROM FAILED_CALLS
                     GROUP BY DESTINATION, ROUTE) f
    ON (f.DESTINATION = dr.DESTINATION AND
        f.ROUTE = dr.ROUTE)

dr подзапрос получает все возможные комбинации DESTINATION и ROUTE - использование UNION DISTINCT указывает, что дубликаты должны быть удалены.

c Затем подзапрос включается в "правильный" счетчик для каждого пункта назначения и маршрута, если такой счет существует.

f Затем подзапрос включается в счетчик "сбой" для каждого пункта назначения и маршрута, если такой счет существует.

Sqlfiddle здесь.

Поделитесь и наслаждайтесь.