title: Get total number of records for groups of data in a table
body: <p>I'm using a SQL Server 2008R2 database. I have a query that I use to get some lab-result data from our database for patients who have had more than 2 visits to our office. Here is the query:</p>
  
  <pre><code>;WITH PatientData (patient_id,last_name, first_name, ethnic_group,
  race, icdcode, encounter_date, test_performed, result_value,
  result_units )
  AS
  (
  SELECT pat.patient_id, pat.last_name, Pat.first_name, pat.ethnic_group,
  pat.race, pl.icdcode, enc.encounter_date, lab.test_performed, lab.result_value,
  lab.result_units
  FROM dbo.dem_patient pat
  RIGHT JOIN dbo.med_problemlist as pl
  ON pat.patient_id = pl.patient_id
  JOIN dbo.enc_encounter as enc
  ON pat.patient_id = enc.patient_id
  JOIN dbo.med_labresult as lab
  ON pat.patient_id = lab.patient_id
  WHERE pl.icdcode like '250%' AND
  enc.encounter_date &gt;= '01/01/2012' AND enc.encounter_date &lt;= '12/31/2012' AND
  pat.last_name != 'test' AND (lab.test_performed = 'HgbA1c' OR 
  lab.test_performed = 'Hemoglobin A1c')
  GROUP BY pat.patient_id, pat.last_name, Pat.first_name, pat.ethnic_group,
  pat.race, pl.icdcode, enc.encounter_date, lab.test_performed, lab.result_value,
  lab.result_units
  )
  SELECT patient_id,last_name, first_name, ethnic_group,
  race, icdcode, encounter_date, test_performed, result_value,
  result_units
  FROM PatientData pd1 
  WHERE EXISTS (SELECT patient_id 
                  FROM PatientData pd2 
                  WHERE pd2.patient_id  = pd1.patient_id  
                  GROUP BY patient_id 
                  HAVING COUNT(*)&gt;1 )
  ORDER BY pd1.race, pd1.ethnic_group
  </code></pre>
  
  <p>This works fine for getting a list of patients and their lab result values. It returns something like this:</p>
  
  <pre><code>patient_id    encounter_date    test_performed    result_value    result_units
  00001         01/15/2012        HgbA1c            5.6             %
  00001         05/03/2012        HgbA1c            8.6             %
  00025         02/02/2012        HgbA1c            9.1             %
  00064         07/01/2012        HgbA1c            7.6             %
  </code></pre>
  
  <p>However, I'd now like to group this data, and get a total number of results between different values of results. For example, I'd like to have results that look something like this:</p>
  
  <pre><code>LessThan7%    7%To8%     8%To9%    GreaterThan9%
  775           289        365       154
  </code></pre>
  
  <p>I've been able to do something similar in the past utilizing the CROSS JOIN function. However, never with a query this complicated. I could really use some help.</p>
  
  <p>Thank you!</p>
  
  <p>UPDATE:</p>
  
  <p>On the advice below, I've updated the query to the following:</p>
  
  <pre><code>;WITH PatientData (patient_id,last_name, first_name, ethnic_group,
  race, icdcode, encounter_date, test_performed, result_value,
  result_units )
  AS
  (
  SELECT pat.patient_id, pat.last_name, Pat.first_name, pat.ethnic_group,
  pat.race, pl.icdcode, enc.encounter_date, lab.test_performed, lab.result_value,
  lab.result_units
  FROM dbo.dem_patient pat
  RIGHT JOIN dbo.med_problemlist as pl
  ON pat.patient_id = pl.patient_id
  JOIN dbo.enc_encounter as enc
  ON pat.patient_id = enc.patient_id
  JOIN dbo.med_labresult as lab
  ON pat.patient_id = lab.patient_id
  WHERE pl.icdcode like '250%' AND
  enc.encounter_date &gt;= '01/01/2012' AND enc.encounter_date &lt;= '12/31/2012' AND
  pat.last_name != 'test' AND (lab.test_performed = 'HgbA1c' OR 
  lab.test_performed = 'Hemoglobin A1c')
  GROUP BY pat.patient_id, pat.last_name, Pat.first_name, pat.ethnic_group,
  pat.race, pl.icdcode, enc.encounter_date, lab.test_performed, lab.result_value,
  lab.result_units
  )
  select sum(case when isnumeric(result_value) = 1
         then (case when result_value &lt; 7.0 then 1 else 0 end) 
         end) as [LessThan7%],
         sum(case when isnumeric(result_value) = 1
         then (case when result_value &gt;= 7.0 and result_value &lt; 8.0 then 1 else 0 end) 
         end) as [7%to8%],
         sum(case when isnumeric(result_value) = 1
         then (case when result_value &gt;= 8.0 and result_value &lt; 9.0 then 1 else 0 end) 
         end) as [8%to9%],
         sum(case when isnumeric(result_value) = 1
         then (case when result_value &gt;= 9.0 then 1 else 0 end)
         end) as [GreaterThan9%]
  FROM PatientData pd1 
  WHERE EXISTS (SELECT patient_id 
                  FROM PatientData pd2 
                  WHERE pd2.patient_id  = pd1.patient_id  
                  GROUP BY patient_id 
                  HAVING COUNT(*)&gt;1 )
  </code></pre>
  
  <p>However, I'm still getting the "Arithmic overflow error converting varchar to data type numeric"</p>
  
  <p>Any additional help is greatly appreciated.</p>