title: Count Records Between Values
body: <p>I have the below query that pulls in max and average cpu and memory values for each server.  </p>
  
  <pre><code>SELECT
      Nodes.Caption, Max(CPULoad.MaxLoad) as CPUMax,
      AVG(CPULoad.AvgLoad) as CPUAvg,
      Round(Max(CPULoad.MaxMemoryUsed / CPULoad.TotalMemory * 100),2) as MemMax, 
      Round(AVG(CPULoad.AvgPercentMemoryUsed),2) as MEMAvg
  FROM            
      Nodes 
  INNER JOIN
      CPULoad ON Nodes.NodeID = CPULoad.NodeID
  WHERE
      Datetime &gt;= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0)        
      AND Datetime &lt; DATEADD(MONTH,datediff(MONTH,0,getdate()),0) 
  </code></pre>
  
  <p>GROUP BY 
          Nodes.Caption</p>
  
  <p>Now I need to figure out the following.</p>
  
  <p>A count of each value (cpumax, cpuavg, memmax, memavg) that are between the following ranges:</p>
  
  <ol>
  <li>'>= 0  &lt;= 10'</li>
  <li>'>= 10 &lt;= 20'</li>
  <li>'>= 20 &lt;= 30'</li>
  <li>'>= 30 &lt;= 40'</li>
  <li>'>= 40 &lt;= 50'</li>
  </ol>
  
  <p>I tried something like <code>SUM(case when Max(CPULoad.MaxLoad) &lt; 10 then 1 else 0 end</code>) but I keep getting an error</p>
  
  <blockquote>
    <p>Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause</p>
  </blockquote>
  
  <p>I tried to add both the entire statement and just the <code>max(cpuload.maxload)</code> in a <code>GROUP BY</code>, but it doesn't like it.</p>
  
  <p>Lastly, I need to group each of the servers by the specific ranges. I.e. all servers that have a cpumax between 0 and 10 along with their corresponding value.</p>