Count another count value but only if it is high enough

I'm trying to pull 2 numbers. One is a total of how many doctors (dr table) have more than 10 answers 开发者_高级运维(answers table) from within 1 month and 75 answers total regardless of the date. The other number is the same thing but for within the last 3 months instead of 1 month.

I used this answer answer below to come up with this query:

SELECT D.name, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as '1 month', 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as '1 quarter', 
    count(DISTINCT A.id) as total
FROM dr D
JOIN answer A ON A.dr_id=D.id AND A.status=3
GROUP BY D.id

This gives me the raw information I need, but I don't know how to count the counts given by comparing them to the 10 and 75 answers requirements.

---

Something like this, I think:

SELECT
  COUNT(CASE WHEN total >= 75 AND `1month`   > 10 THEN name END) AS `10+ per month count`,
  COUNT(CASE WHEN total >= 75 AND `1quarter` > 10 THEN name END) AS `10+ per quarter count`
FROM (
  SELECT D.name, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as `1month`, 
    count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as `1quarter`, 
    count(DISTINCT A.id) as total
  FROM dr D
    JOIN answer A ON A.dr_id=D.id AND A.status=3
  GROUP BY D.id
) s

---

You might need to play w/ this query a bit, but it should give you what you're looking for. Basically take your query and use it as a derived table and summarize it further using group by/having.

;with DrCounts as (
    SELECT D.id, 
      count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 1 MONTH) then A.id end) as '1month', 
      count(DISTINCT case when A.created > DATE_SUB(NOW(), INTERVAL 3 MONTH) then A.id end) as '1quarter', 
      count(DISTINCT A.id) as total
    FROM dr D
    JOIN answer A ON A.dr_id=D.id AND A.status=3
    GROUP BY D.id)

select count(distinct D.id) as Dr1075
from  DrCounts D
group by D.Id
having D.total >= 75 and D.1month >= 10
union
select count(distinct D.id) as Dr1075
from  DrCounts D
group by D.Id
having D.total >= 75 and D.1quarter >= 10