#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    1
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Code:
    select staff_name,
           case_date,
           total_cases / passed_cases as percentage
    from (
        SELECT staff_name, 
               case_date, 
               Count(result) as total_cases,
               count(case when result = 'pass' then 1 else null end) as passed_cases
        FROM results
        GROUP BY staff_name, case_date;
    ) t
    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

IMN logo majestic logo threadwatch logo seochat tools logo