Can anyone help me with the following query?
SELECT
voc.username AS Medewerker,
voc.bestvastonbep_count AS Doorgezet,
IFNULL(cc.call_count, 0) AS Telefoongesprekken,
voc.bestvastonbep_count / IFNULL(cc.call_count, 0) AS Ratio
FROM (
SELECT
u.id AS user_id,
CONCAT(u.firstname, ' ', u.lastname) AS username,
COUNT(vo.id) AS bestvastonbep_count
FROM `bestvastonbep` vo
JOIN `best` b ON b.id = vo.best_id
JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
JOIN `user` u ON u.id = b.user_id
WHERE vos.type = 2
AND vos.status = 2
AND (b.date BETWEEN '2017-02-04' AND '2017-05-04')
GROUP BY u.id
) voc
LEFT JOIN (
SELECT
user_id,
COUNT(id) AS call_count
FROM `call` c
WHERE (c.start BETWEEN '2017-02-04' AND '2017-05-04')
AND c.duration > 0
AND c.calltype = 'out'
GROUP BY c.user_id
) cc
ON voc.user_id = cc.user_id;
It produces the desired result, but it's immensely slow.
I've read questions from other people about similar queries. For them it turned out that joining subqueries wasn't necessary.
The difference for me is that I'm using GROUP
for both subqueries.
Hopefully there's a way and I'm just not seeing it.