title: Selecting counts of a substring
body: <p>I know this has to be an easy select but I am having no luck figuring it out. I've got a table has a field of customer grouping codes and I'm trying to get a count of each distinct character 2 through 6 sets. In my past foxpro experience a simple  </p>
  
  <p><code>select distinct substr(custcode,2,5), count (*) from a group by 1</code>  </p>
  
  <p>would work, but this doesn't appear to work in sql server queries. The error message indicated it didn't like using the number reference in the group by so I changed it to custcode but the count just returns 1 for each, as I assume the count is after the distinct occurs so there is only one. If I change the count to <code>count(distinct substring(custcode,2,5))</code> and remove the first distinct substring I just get a count of how many different codes exist. Can someone point out what I'm doing wrong here? Thanks.</p>