How do I write a query that returns the number of records in a particular status out of the last twenty (but only if those twenty happened today?)l

I need to write a query that will tell me how many times a test failed in the last twenty attempts (but only counting attempts made today and only开发者_JAVA百科 if there have been at least twenty attempts today.) I'm particularly interested in just the ones that have failed 10 times or more in the last 20 iterations.

Schema:

test_results
-----------------
id
test_id
status (0 failed, 1 passed)
date_ran 

This is how I far I got before losing the plot:

SELECT count(*) as num_failed, test_id 
FROM test_results
WHERE
date_ran > '2011-03-21 00:00:00'
AND
    status = 0
AND
    test_id IN ( -- some sort of subquery that gives me tests that have run
                  at least 20 times today, regardless of status --)
GROUP BY test_id
HAVING num_failed > 9
ORDER BY date_ran DESC

---

The following should return all tests that have run at least 20 times today, of which at least 10 failed.

select test_id
      ,count(*)               as num_runs
      ,sum(status)            as passed
      ,count(*) - sum(status) as failed
  from test_results
 where date_ran = current_date
 group 
    by test_id 
having count(*) >= 20
   and count(*) - sum(status) >= 10;

I exploited the fact that status=1 can be summed. If you have other values for status, you would need to use a CASE/WHEN expression instead.

---

use Limit 0, 20 and also instead of using num_failed in HAVING, you need to use count(*) directly-

SELECT count(*) as num_failed, test_id 
FROM test_results
WHERE
date_ran > '2011-03-21 00:00:00'
AND
    status = 0
AND
    test_id IN ( -- some sort of subquery that gives me tests that have run
                  at least 20 times today, regardless of status --)
GROUP BY test_id
HAVING count(*) > 9
ORDER BY date_ran DESC
LIMIT 0,20

---

select test_id,sum(status=0) as num_failed,count(*) as total from test_results as t1
where (select count(*) from test_results as t2
       where t1.test_id = t2.test_id and t1.date_ran < t2.date_ran) <20 and date_ran > curdate()
group by test_id
having num_failed > 9 and total = 20