Jack Jack - 3 months ago 14
MySQL Question

combining two sum results?

I'm trying to view Antivirus events that will show the number of event for the AV and firewall within a 24 hour period. Basically I'm trying to sum the same column under different condition. I've tried using a CASE WHEN statement in the SUM function, but it does not return any results.

SELECT
events.arc_sourceHostName,
SUM(CASE WHEN events.arc_deviceEventClassId="fw" THEN events.arc_baseEventCount ELSE 0 END) AS "FWCount",
SUM(CASE WHEN events.arc_deviceEventClassId="av" THEN events.arc_baseEventCount ELSE 0 END) AS "AVCount"

FROM events

WHERE
events.arc_deviceVendor = "BitDefender"
AND
(events.arc_deviceEventClassId="fw"
OR
events.arc_deviceEventClassId="av")


GROUP BY
events.arc_sourceHostName

ORDER BY FWCount DESC


ADDITIONAL DETAILS

expecting to see the hostname followed by the number of AV event, followed by the number of firewall events.

Hostname AVCount FWCount
Host1 5 4
Host2 3 5

Answer

Does this query do what you want?

SELECT e.arc_sourceHostName,
       SUM(CASE WHEN e.arc_deviceEventClassId = 'fw' THEN e.arc_baseEventCount ELSE 0 END) AS FWCount,
        SUM(CASE WHEN e.arc_deviceEventClassId = 'av' THEN e.arc_baseEventCount ELSE 0 END) AS AVCount
FROM events e
WHERE e.arc_deviceVendor = 'BitDefender' AND
      e.deviceEventClassId IN ('fw', 'av')
GROUP BY e.arc_sourceHostName
ORDER BY FWCount DESC;

If this doesn't work then no rows may meet the WHERE conditions.

Comments