Query DB for Call Total etc 1

[Sitehelp]

Hello all, ok I want to query mySQL database for my web site. I have the columns StaffID, CallID and status in the DB. Status is either open or closed. StaffID is a list off all users, and finally callID is the call number they closed i.e. whether they closed call 1 or call 2 etc.

So in my DB there is repeating staffIDs as they can close more than one call. For example:

CallID Status StaffID
1 Open JBloggs
2 Closed JJones
3 Closed JJones
4 Closed Howen

What I want is to query the DB and say. "List off all the staffids and the number of calls they have closed" This therefore will list all there names in order of the number of calls they have closed e.g.

StaffID
JJones 2
HOwen 1
JBloggs 0

Any ideas how to do this as I am completely out of ideas! thanks, this will be really useful to me! cheers!

 

[nigelrivett]

select StaffID, count(*)
from tbl
where Status = 'Closed'
group by StaffID
order by count(*) desc


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Sitehelp]

Excellent that worked! I want to add an extra column to the results, I am using:

SELECT count(callID)
FROM callinfo
WHERE callinfo.StaffID = '$row_Leader1.StaffID'

Where '$row_Leader1.StaffID' is the StaffID result from the last query as its printing out in a dynamic table in Dreamweaver. However the code above does not work its only printing 0 for all users. Its supposed to list the total number of calls that are open or closed that are under each users name. Does this make sense! any ideas! thanks

 

[Sitehelp]

Actually I have it now printing all the calls that are open for the first StaffID but not for the rest????

SELECT StaffID, count(CallID)
FROM callinfo
WHERE callinfo.CallStatus = 'Closed'
GROUP BY StaffID
ORDER BY CallID ASC

The rest just get the same number of calls as the first for some reason!????????????????