Need Help with mysql query

[vicmad]

I'm running SNMP Zenoss connected to mysql database.I'm trying to select the device that fails the most from the
database.
Column name called "devices" has all the device names.
Column name called "events" has all the events that are generated.
How can i write a query that will display the device that fails the most in a month?

petrosky suggested the below query:
SELECT Devices, SUM(`Events`) AS Total FROM devices
GROUP BY Devices
ORDER BY Total DESC LIMIT 1

The above query works and displays the device that fails the most.
What I also need is that it should display the event from the "events" column.

Please help and thanks in advance.

 

[jaxtell]

What have you tried so far? Are you expecting that someone here will just do the work for you, or did you get stuck trying to figure it out yourself?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Kozusnik]

Add "events" to your select and group by statements. Personally, I would have used something like this...

SELECT * from (
SELECT devices, events, SUM(*) AS Total
FROM devices
GROUP BY devices, events) as x
ORDER BY Total DESC

Now you can see all failing devices in descending order.

 

[r937]

kozusnik, i don't think [red]SUM(*)[/red] is valid SQL

vicmad, i don't think SUM(events) is right either, unless events is actually a numeric value that can be summed up

i think this is what you want --

SELECT Devices
     , [blue]COUNT(*)[/blue] AS number_of_events 
  FROM devices
GROUP 
    BY Devices
ORDER 
    BY number_of_events DESC LIMIT 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Kozusnik]

My apologies. I was thinking COUNT(*). Of course SUM(*) won't work. Thanks.

 

[vicmad]

I like you thank everybody for all the help. You guys rock!!