a query to count the frequency of an occurence

[ts568]

Is there a way of writing a query that looks at a table and works out from staffNumber, which staff members number appears most on the table and then links back to the STAFF table and shows their name?

So there are two tables

ORDERSPROCESSED and STAFF

And I want to view which staff member has processed the most orders by looking in the staffNumber column, and then once I have found it, I look in my STAFF table for that staffNumber and then give that result.

 

[PHV]

SELECT S.staffNumber, S.staffName, Count(*) As Orders
FROM STAFF As S INNER JOIN ORDERSPROCESSED AS O ON S.staffNumber = O.staffNumber
GROUP BY S.staffNumber, S.staffName
ORDER BY 3 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

ts,
I would really recommend reading the Understanding SQL Join article linked below. It will help explain some of the basics of SQL and how to extract the data and give you a better understanding.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins