SQL Query to calculate staff pass rates from a testing results table

Hi,

Im looking for some help building a query on the below table which stores the results of testing carried out on work cases:

case_id staff_name result case_date
1 dave pass 01-May-12
2 dave fail 01-May-12
4 jim pass 01-May-12
5 dave pass 02-May-12
6 dave pass 02-May-12



I want the query to return for each member of staff who has had a case tested in a day a pass rate for that member of staff that day. The result would look something like:

case_date staff_name tests passes score
01-May-12 dave 2 1 50%
01-May-12 jim 1 1 100%
02-May-12 dave 2 2 100%



I have managed to make separate queries for getting the number of tests per staff per day

SELECT staff_name, Count(result), case_date
FROM results
GROUP BY staff_name, case_date;

SELECT case_date, staff_name, Count(result)
FROM results
WHERE result="pass"
GROUP BY staff_name, case_date

But i dont know how to combine the 2 and then work out a percentage in SQL.

Can anyone help?

Thanks

Mike

In the future please make your code readable by formatting it using [code] tags.

See here for details: http://forums.devshed.com/misc.php?do=bbcode