Using having count cause between two numbers, so I can use a range inside the having count clause

Question

select patientid, illnessid, count(1) as myrange    
from database (nolock)    
where diagnosetime between '2020-08-27' and '2020-08-28'    
group by PJMBatchId, PJMCltId
HAVING COUNT(*) >= 2;
having count (*) >= 5;

So I want to combine my having count so it can count between 2 and 5, is there way to do it,

Answer 1

Use BETWEEN:

SELECT patientid, illnessid, COUNT(1) AS myrange
FROM database (nolock)
WHERE diagnosetime BETWEEN '2020-08-27' AND '2020-08-28'
GROUP BY PJMBatchId, PJMCltId
HAVING COUNT(*) BETWEEN 2 AND 5;

If you don't want to use BETWEEN in your having clause, you could just and together the two inequalities:

HAVING COUNT(*) >= 2 AND COUNT(*) <= 5;