Consult SQL for count hosts UP and DOWN from zabbix groups

CountHosts.sql

SELECT
    g.name AS grupo,
    COUNT(DISTINCT h.hostid) AS total,
    SUM(CASE WHEN ts.value = 0 THEN 1 ELSE 0 END) AS up,
    SUM(CASE WHEN ts.value = 1 THEN 1 ELSE 0 END) AS down
FROM
    hosts h
JOIN
    hosts_groups hg ON h.hostid = hg.hostid
JOIN
    hstgrp g ON hg.groupid = g.groupid
LEFT JOIN (
    SELECT
        h.hostid AS hostid,
        t.value AS value
    FROM
        hosts h
    JOIN
        items i ON h.hostid = i.hostid
    JOIN
        functions f ON i.itemid = f.itemid
    JOIN
        triggers t ON f.triggerid = t.triggerid
    WHERE
        i.key_ = 'icmpping'
) ts ON h.hostid = ts.hostid
WHERE
    g.name IN ('Servidores Linux', 'Firewalls')
GROUP BY
    g.name
ORDER BY
    total DESC;