[SOLVED] Help with a calculation query

[Zergman]

Im really not sure how to describe this so please bare with me on this.

 

I have a column in the database that stores our employee ID's whenever a new incident is submitted to the database.

 

What I was asked is to have a display of the top 5 ID's that call in the most.

 

So what I need is a query that will calculate the amount of ID's are in the database, count how many times each one called in and display the top 5 highest amount of ID's.

 

.... hope I explained that right.

 

Don't even know where to start with this.

[fenway]

Why not a COUNT() with GROUP BY?

[Maq]

Something like this perhaps?  Not tested*

 

SELECT COUNT(id), id FROM table ORDER BY COUNT(id) DESC LIMIT 5;

[corbin]

Something like this perhaps?  Not tested*

 

SELECT COUNT(id), id FROM table ORDER BY COUNT(id) DESC LIMIT 5;

 

 

Errrrr....?  Huh?

 

 

Did you mean to throw a GROUP BY in there?

[Zergman]

I tried the example using GROUP BY and when I echo the query, I get

 

Resource id #130

 

Here's the one in the page

SELECT COUNT(flagentTID), flagentTID FROM data GROUP BY (flagentTID) DESC LIMIT 5

[Zergman]

Hitting the gigerweb modified the query a bit but oddly getting the same result when I echo it

 

SELECT COUNT(flagentTID) FROM data GROUP BY flagentTID DESC LIMIT 5

 

Gives me

Resource id #130 
Resource id #130 
Resource id #130 
Resource id #130 
Resource id #130 

[Zergman]

Researching Resource id messages, it looks like its the way im trying to output it on the page.

 

I know a simple echo won't work, whats the best way?

[Barand]

$sql = "SELECT flagentTID, COUNT(*) as total
          FROM data 
          GROUP BY flagentTID 
          ORDER BY total DESC 
          LIMIT 5";
$res = mysql_query($sql);
while (list($id, $tot) = mysql_fetch_row($res))
{
    echo "$id : $tot <br />";
}

[Zergman]

Perfect, works like a charm!

 

Thansk for the example Barand!