Select with Conditional COUNT

Question

Hello everyone and thanks for the help in advance. In a prevous post https://learn.microsoft.com/en-us/answers/questions/2148980/counting-occurrences-in-a-table, Erland Sommarskog built a query for me that counted the occurrences of vaccines by patient. The query:

SELECT MRNumber, tblPtmstr1.PatientLastName, tblPtmstr1.PatientFirstName, tblPtmstr1.PatientDOB,
       COUNT(CASE WHEN vaccine = 'DTaP' THEN 1 END) AS DTaP,
       COUNT(CASE WHEN vaccine = 'IPV' THEN 1 END) AS IPV,
	   COUNT(CASE WHEN vaccine = 'MMR' THEN 1 END) AS MMR,
	   COUNT(CASE WHEN vaccine Like '%Hib%' THEN 1 END) AS Hib,
	   COUNT(CASE WHEN vaccine = 'Hep B, adolescent or pediatric' THEN 1 END) AS HepB,
	   COUNT(CASE WHEN vaccine = 'varicella' THEN 1 END) AS VZW,
	   COUNT(CASE WHEN vaccine Like '%PCV%' THEN 1 END) AS PCV
	   /*
	   COUNT(CASE WHEN vaccine = 'Pneumococcal conjugate PCV 13' THEN 1 END) AS PCV13,
	   COUNT(CASE WHEN vaccine = 'Pneumococcal conjugate PCV20, polysaccharide CRM197 conjugate, adjuvant, PF' THEN 1 END) AS PCV20,
	   */
	   
      
FROM  tbl_Log_ImmunizationRegistry AS  tblVaccines
LEFT JOIN tblPtmstr1 on tblVaccines.MRNumber = tblPtmstr1.PatientID
Where Convert(date, tblVaccines.PatientDOB) Between '01/01/2022 12:00 am' and '12/31/2023 11:59 pm'
GROUP BY MRNumber, tblPtmstr1.PatientLastName, tblPtmstr1.PatientFirstName, tblPtmstr1.PatientDOB

I need to now refine this query by displaying only when a count reaches a threshold, for example DTap >= 4 and IPV >= 3. When I place this parameter in the WHERE clause, I receive the error message DTap is an invalid column. I think I understand the column name is not created due to execution order, but I'm trying to understand how to make this work. Perhaps a temporary table? Any help ould be appreciated.

Answer 1

the where clause works at the row level. if you want to filter on aggregate values, then the were needs to use a having or a sub select. ... where (select count...) ...) < 5 .

your other option is use CTE:

WITH Totals as 
(
   SELECT MRNumber, tblPtmstr1.PatientLastName, tblPtmstr1.PatientFirstName, tblPtmstr1.PatientDOB,
       COUNT(CASE WHEN vaccine = 'DTaP' THEN 1 END) AS DTaP,
       COUNT(CASE WHEN vaccine = 'IPV' THEN 1 END) AS IPV,
	   COUNT(CASE WHEN vaccine = 'MMR' THEN 1 END) AS MMR,
	   COUNT(CASE WHEN vaccine Like '%Hib%' THEN 1 END) AS Hib,
	   COUNT(CASE WHEN vaccine = 'Hep B, adolescent or pediatric' THEN 1 END) AS HepB,
	   COUNT(CASE WHEN vaccine = 'varicella' THEN 1 END) AS VZW,
	   COUNT(CASE WHEN vaccine Like '%PCV%' THEN 1 END) AS PCV
   FROM  tbl_Log_ImmunizationRegistry AS  tblVaccines
   LEFT JOIN tblPtmstr1 on tblVaccines.MRNumber = tblPtmstr1.PatientID
   Where Convert(date, tblVaccines.PatientDOB) Between '01/01/2022 12:00 am' and '12/31/2023 11:59 pm'
   GROUP BY MRNumber, tblPtmstr1.PatientLastName, tblPtmstr1.PatientFirstName, tblPtmstr1.PatientDOB
)
select *
from Totals
where DTaP < 5;

Answer 2

I think you may need a HAVING clause instead. For example:

HAVING COUNT(CASE WHEN vaccine = 'DTaP' THEN 1 END) >= 4