CODEWITHSUNDEEP
sqlsql-server-2008selectcountsubstring
user1572700
user1572700

Reputation: 133

Selecting counts of a substring

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

select distinct substr(custcode,2,5), count (*) from a group by 1

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 count(distinct substring(custcode,2,5)) 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.

Upvotes: 4

Views: 18250

Answers (1)

Hart CO
Hart CO

Reputation: 34774

The DISTINCT and GROUP BY are redundant, you just want GROUP BY, and you want to GROUP BY the same thing you are selecting:

select substr(custcode,2,5), count (*) 
from a 
group by substr(custcode,2,5)

In SQL Server you can use column aliases/numbers in the ORDER BY clause, but not in GROUP BY.

Ie. ORDER BY 1 will order by the first selected column, but many consider it bad practice to use column indexes, using aliases/column names is clearer.

Upvotes: 6

Related Questions