title: MYSQL- Having with between returns empty result
body: <p>Following is the query i am trying to run.</p>
  
  <pre><code>SELECT p_date,TS.substationid,ts.substationcode,ts.manufacturingproductid,ts.assemblylineid,ts.teamid,ts.subofficecode,
  TA.AllowID,ta.allowname,ta.minbenchmark,ta.maxbenchmark,COUNT(vfp.UnitsPass) Achieved,
              CASE TA.RewardType 
                      WHEN 'Monthly' THEN 
                            TA.RewardValue/DAY(LAST_DAY(P_Date))
                  WHEN 'Yearly' THEN
                            TA.RewardValue/((STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM P_Date),1,1),'%Y%m%d')) /(STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM P_Date),12,31), '%Y%m%d')))
                  WHEN 'Daily' THEN
                            (TA.RewardValue*12)/365           
                   END-- , 0) 
                   AS Allowance
  FROM tempsubstation ts
  LEFT JOIN TempAllowances ta ON ts.manufacturingproductid=ta.manufacturingproductid AND ts.subofficecode=ta.subofficecode
  LEFT JOIN wms_vfproduction vfp ON DATE(vfp.CreationDate)=p_date AND vfp.StationCode='1600150'
  WHERE ta.AllowID=41 AND vfp.UnitsPass=1
   GROUP BY p_date,substationcode
   HAVING COUNT(vfp.UnitsPass) BETWEEN ta.minbenchmark AND ta.maxbenchmark
  </code></pre>
  
  <p>Adding having with between starts returning empty results.while when i was comparing it with hard coded values it returns results (again, without between). to keep it generalized i cant use hard coded values and it does not work with between even if i give it hard coded values.</p>
  
  <p><strong>UPDATE</strong>: Having does not work with between because it already selects a result set with a range where the max is less than the result of COUNT(). However, there is always at least one range with its max greater than the result of COUNT().</p>