title: SQL - How to return only rows which have a value which appears more than once?
body: <p>I'm fairly new to SQL server...but I have a problem. I've written the below query, which works just fine for displaying all visits (encounters) and lab results for a patient in 2012.  </p>
  
  <pre><code>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 '401.9%' 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
  ORDER BY race, ethnic_group
  </code></pre>
  
  <p>The result from the above query looks something like this:</p>
  
  <pre><code>patient_id  last_name  first_name  ethnic_group  race  icdcode  encounter_date lab_result
  0           smith      john        HIS           WHI   401.90   01/01/2012     9.1
  0           smith      john        HIS           WHI   401.90   10/12/2012     9.2
  0           smith      john        HIS           WHI   401.90   11/15/2012     9.7
  5           doe        jane        NSP           BLK   401.90   01/20/2012     11
  6           davids     david       NSP           BLK   401.90   04/25/2012     5.9
  6           davids     david       NSP           BLK   401.90   05/04/2012     6.4
  </code></pre>
  
  <p>However, I now need to return only those patients who have more than one visit (encounter) between 1/1/2012 and 12/31/2012. It would look like this (from the above result)</p>
  
  <pre><code>patient_id  last_name  first_name  ethnic_group  race  icdcode  encounter_date lab_result
  0           smith      john        HIS           WHI   401.90   01/01/2012     9.1
  6           davids     david       NSP           BLK   401.90   04/25/2012     5.9
  </code></pre>
  
  <p>Can anyone help me with this?   </p>
  
  <p>UPDATE: So, using the suggestions below, I've added HAVING COUNT(*) > 1 to the query. However, that gives me some results I don't quite understand. </p>
  
  <p>Results of query without the HAVING COUNT(*) > 1 statement:</p>
  
  <pre><code>1001131     BLA 401.9   48:48.3 Hemoglobin A1c  9.9
  1001131     BLA 401.9   00:00.0 Hemoglobin A1c  9.9
  1001131     BLA 401.9   44:44.3 Hemoglobin A1c  9.9
  1001648     BLA 401.9   50:15.6 Hemoglobin A1c  6.5
  1001648     BLA 401.9   16:15.1 Hemoglobin A1c  6.5
  1001648     BLA 401.9   28:04.5 Hemoglobin A1c  6.5
  1001648     BLA 401.9   17:00.2 Hemoglobin A1c  6.5
  1002648     BLA 401.9   17:47.8 Hemoglobin A1c  6.1
  1002648     BLA 401.9   52:38.0 Hemoglobin A1c  6.1
  1002648     BLA 401.9   43:47.3 Hemoglobin A1c  6.1
  1002648     BLA 401.9   02:55.9 Hemoglobin A1c  6.1
  1002715     BLA 401.9   37:52.4 Hemoglobin A1c  13.1
  1002715     BLA 401.9   44:41.0 Hemoglobin A1c  12.6
  1002715     BLA 401.9   44:41.0 Hemoglobin A1c  13.1
  1002715     BLA 401.9   37:52.4 Hemoglobin A1c  10.7
  1002715     BLA 401.9   37:52.4 Hemoglobin A1c  12.6
  1002715     BLA 401.9   00:57.9 Hemoglobin A1c  10.7
  1002715     BLA 401.9   00:57.9 Hemoglobin A1c  12.6
  1002715     BLA 401.9   00:57.9 Hemoglobin A1c  13.1
  1002715     BLA 401.9   38:17.6 Hemoglobin A1c  10.7
  1002715     BLA 401.9   38:17.6 Hemoglobin A1c  12.6
  1002715     BLA 401.9   38:17.6 Hemoglobin A1c  13.1
  1002715     BLA 401.9   44:41.0 Hemoglobin A1c  10.7
  1002893     BLA 401.9   33:45.4 Hemoglobin A1c  5.9
  1002893     BLA 401.9   22:58.5 Hemoglobin A1c  5.9
  1002893     BLA 401.9   00:00.0 Hemoglobin A1c  5.9
  1002893     BLA 401.9   55:11.3 Hemoglobin A1c  5.9
  1003195     BLA 401.9   34:08.7 Hemoglobin A1c  6.4
  1003195     BLA 401.9   50:03.6 Hemoglobin A1c  6.4
  1003195     BLA 401.9   08:36.8 Hemoglobin A1c  6.4
  1003195     BLA 401.9   33:56.6 Hemoglobin A1c  6.4
  1003195     BLA 401.9   34:04.3 Hemoglobin A1c  6.4
  </code></pre>
  
  <p>Results of query with the HAVING statement:</p>
  
  <pre><code>1001937     BLA 401.9   11/14/12Hemoglobin A1c  6.3
  1001937     BLA 401.9   8/14/12 Hemoglobin A1c  6.3
  1001937     BLA 401.9   5/14/12 Hemoglobin A1c  6.3
  1001937     BLA 401.9   2/14/12 Hemoglobin A1c  6.3
  1001937     BLA 401.9   11/14/12Hemoglobin A1c  6.3
  1002034     BLA 401.9   1/10/12 HEMOGLOBIN A1c  7.7
  1002034     BLA 401.9   6/8/12  HEMOGLOBIN A1c  7.7
  1002034     BLA 401.9   9/7/12  HEMOGLOBIN A1c  7.7
  1002034     BLA 401.9   10/15/12HEMOGLOBIN A1c  7.7
  1002648     BLA 401.9   2/22/12 Hemoglobin A1c  6.1
  1002648     BLA 401.9   7/2/12  Hemoglobin A1c  6.1
  1002648     BLA 401.9   10/2/12 Hemoglobin A1c  6.1
  1002648     BLA 401.9   2/22/12 Hemoglobin A1c  6.1
  1020923     BLA 401.9   1/30/12 HEMOGLOBIN A1c  10
  1020923     BLA 401.9   1/30/12 Hemoglobin A1c  11.8
  1020923     BLA 401.9   4/17/12 Hemoglobin A1c  11.8
  1020923     BLA 401.9   5/16/12 HEMOGLOBIN A1c  10
  1020923     BLA 401.9   5/16/12 Hemoglobin A1c  11.8
  1020923     BLA 401.9   9/13/12 HEMOGLOBIN A1c  10
  1020923     BLA 401.9   9/13/12 Hemoglobin A1c  9.3
  1020923     BLA 401.9   10/1/12 HEMOGLOBIN A1c  10
  1020923     BLA 401.9   10/1/12 Hemoglobin A1c  11.8
  </code></pre>