title: Collapse and summarize counts within group by
body: <p>I have the following set of data:</p>
  
  <pre><code>SalesPerson PackageHistoryID    PackageID   SalesPersonID   EnrollmentAmount    PackageType
  -------------------------------------------------------------------------------------------
  Jim Jones   2895                310         59019           27.15               New Member
  Jim Jones   2895                310         59019           53.21               New Member
  Jim Jones   2895                310         59019           42.35               New Member
  Jim Jones   2916                221         59019           379.01              Renewal
  Jim Jones   2932                326         59019           53.21               New Member
  Jim Jones   2932                326         59019           27.15               New Member
  Jim Jones   2933                326         59019           53.21               Renewal
  Jim Jones   2933                326         59019           27.15               Renewal
  </code></pre>
  
  <p>Upon that data set I run the following query:</p>
  
  <pre><code>select Salesperson, PackageType, count(*) AS Packages, sum(EnrollmentAmount) AS Enrollment
  from Sales2
  group by SalesPerson, PackageType
  order by SalesPerson, PackageType
  </code></pre>
  
  <p>...and I get these results:</p>
  
  <pre><code>Salesperson    PackageType    Packages     Enrollment
  ----------------------------------------------------
  Jim Jones      New Member     5            203.07
  Jim Jones      Renewal        3            459.37
  </code></pre>
  
  <p>My final results as shown above are almost perfect. The only problem is the counts in the <code>Packages</code> column. Instead of 5 and 3, the counts should be 2 and 2, because I want it to indicate the number of PackageTypes per PackageHistoryID, not per EnrollmentAmount. I want the EnrollmentAmounts summed so the records can be compressed such that PackageHistoryID never repeats. The first data set shown manifests a 1-many relationship between PackageHistory records and EnrollmentAmount. I thought my 2nd query (the group by) would aggregate this correctly but you can see that it shows 8 total PackageHistories when it really should only show 4.</p>
  
  <p>Here is how the final result set should look:</p>
  
  <pre><code>Salesperson    PackageType    Packages     Enrollment
  ----------------------------------------------------
  Jim Jones      New Member     2            203.07
  Jim Jones      Renewal        2            459.37
  </code></pre>
  
  <p>The 2 and 2 indicate the fact that there are really only 4 PackageHistory records in the result set; 2 are New Member and 2 are Renewal. The multiple EnrollmentAmount records are causing too many records and thus the counts get wrongly expanded in the final query.</p>
  
  <p><strong>Important note</strong>: Although SalesPerson is always the same in the results shown, these can sometimes be different, though they will be the same for any given PackageHistory (1-1). The grouping needs to be (1) by SalesPerson, then (2) by PackageType, and summarize/flatten the EnrollmentAmounts within each unique PackageHistory.</p>
  
  <p>What query will give me correct results?</p>