SELECT 
    t.ind, c1.comments_scout, c2.comments_client
FROM
    portal.tickets AS t
        LEFT JOIN
    (SELECT 
        c.edit_ind AS ticket_id, COUNT(c.ind) AS comments_scout
    FROM
        portal.comments AS c
    LEFT JOIN portal.scout_users AS su ON su.ind = c.name
    LEFT JOIN portal.access_group AS ag ON ag.id = su.access_group
    WHERE
        ag.type = 'scout-integrator'
            AND c.comment NOT LIKE '%Заявка закрыта%'
            AND c.comment NOT LIKE '%Перенос срока решения заявки отменён%'
            AND c.comment NOT LIKE '%Ответственный за решение установил новый срок решения заявки%'
            AND c.deny != 2
            AND c.active != 2
    GROUP BY c.edit_ind) AS c1 ON c1.ticket_id = t.ind
        LEFT JOIN
    (SELECT 
        c.edit_ind AS ticket_id, COUNT(c.ind) AS comments_client
    FROM
        portal.comments AS c
    LEFT JOIN portal.scout_users AS su ON su.ind = c.name
    LEFT JOIN portal.access_group AS ag ON ag.id = su.access_group
    WHERE
        ag.type = 'client'
            AND c.comment NOT LIKE '%Заявка закрыта%'
            AND c.active != 2
    GROUP BY c.edit_ind) AS c2 ON c2.ticket_id = t.ind
ORDER BY t.ind DESC