SQL Query Statement

[vicmad]

I'm running SNMP Zenoss connected to mysql database.I'm trying to select the device that fails the most based on the sevrity level 5 and 4. Severity 5 is highest and 1 is the lowest. Table name is history.
-Column name called "devices" has all the device names.
-Column name called "events" has all the events that are generated.
-Column name called "severity" has severity levels from 5-1.

BELOW IS THE QUERY:
SELECT device,
count(*) AS number_of_events
from history
where severity = 5 and 4
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10

The query displays the top 10 devices that fail based on severity 5. I need based on severity 5 and 4.

PLEASE HELP

 

[gmmastros]

Do you need the top 10 of each, or just 10 rows with a mixture of 4 and 5 based on the data?

This forum is for Microsoft SQL Server (not MySQL). If the following change does not work for you, I suggest you post in a MySQL specific forum instead.

Try this:

SELECT device,
       count(*) AS number_of_events
from   history
where  [!]severity = 5 Or severity = 4[/!]
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try using UNION ALL, e.g.

SELECT device,
count(*) AS number_of_events
from history
where severity = 5
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10
UNION ALL
SELECT device,
count(*) AS number_of_events
from history
where severity = 4
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10

Although I don't know if this will work for MySQL.

PluralSight Learning Library

 

[vicmad]

Thanks.

That did not work. Your query gives me the top 10 based on severity 4 for some reason.
I am not looking for the top 10 of each. I need a mixturre of 4 and 5.

Thanks again.

 

[vicmad]

Thanks Markros.
That worked except i had to remove one statement "ORDER BY number_of_events DESC LIMIT 10"

SELECT device,
count(*) AS number_of_events
from history
where severity = 5
GROUP BY Device
//ORDER BY number_of_events DESC LIMIT 10
UNION ALL
SELECT device,
count(*) AS number_of_events
from history
where severity = 4
GROUP BY Device
ORDER BY number_of_events DESC LIMIT 10