help using a COUNT function...

[acook]

Does anyone know if this is possible:

I'm running a query against a db and am pulling name, ticket #, group, etc (for a helpdesk)...

What I'd like to do is take the results from the "Name" field, but only show EACH NAME ONCE in a table. For example, I might get 200 results back, but only want to show the name of the person once, with how many tickets they have assigned to them.

This is what my query looks like now:

$query = "SELECT Create_Date, Request_Type, Assignee_Group, Assignee_Individual, Status, Request_Task_ID FROM Request_Task WHERE (Status='Active' OR Status='Assigned' OR Status='Acknowledged' OR Status='Held' OR Status='Pending' OR Status='Pending Cancellation' OR Status='WIP') AND (Assignee_Group='Security')";

So I would want to isolate "Assignee_Individual" and I guess count how many of each type of ticket that person had. And the table would look like:

___NAME___|___Assigned___|___Acknowledged___|___Held___|___Pending___|___WIP___
  Mark             5                 6              11            3           5

It seems like it would only need to COUNT the number of each type of ticket assigned to each associate. Does anyone know to start tackling this project?

[shu]

It's probably better just to group by the Name and status:

$query = "SELECT Assignee_Individual, Status, COUNT(*) AS num 
FROM Request_Task WHERE Assignee_Group='Security' 
GROUP BY Assignee_Individual, Status ORDER BY Assignee_Individual, Status";

This should give you a total for each individual per status, result would be like:

Mark Assigned 5
Mark Acknowledged 6
Mark Held 11
Mark Pending 3
Mark WIP 5
....

You then will have to do some creative loops with the result to present the data you way you wanted.

[acook]

Shu,

Thanks for the suggestion. I tested out your code, just to see what would happen and I just get a result that says:

Resource id #3

I tried to keep the code as simple as possible:

<?php


$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');

if ($conn <= 0) {
	echo "Error in connection<BR>";
        exit;
        };

//THE QUERY

$query = "SELECT Assignee_Individual, Status, COUNT(*) AS num 
FROM Request_Task WHERE Assignee_Group='Security' 
GROUP BY Assignee_Individual, Status ORDER BY Assignee_Individual, Status"; 


//THE EXECUTION

$result = odbc_exec($conn, $query) or die("Query failed, could not connect to table.  Are you sure it exists?");



echo "$result";


odbc_close($conn);
?>

If I try to add a loop like:

while(odbc_fetch_row($result))

{
echo "$result";
}

I just get a blank page (no results). Any thoughts on this?

[liquorvicar]

Try

while($row=odbc_fetch_row($result))

{
var_dump($row);
}

[acook]

Hmmm, I'm still getting a result that says:

Resource id #3