Getting a count of items

I'm sure there is an easy solution to this, but I always end up
resorting to using cursors, which I know is bad. Maybe somebody can
help me bridge the gap on this one
Assume I have a table like:
Customers
CustomerName nvarchar(50)
State nvarchar(50)
Assume I'm looking for a count of customers in each state, like:
WA 50
ID 24
VA 36
In my current solution, I select all of the distinct states into a
temporary table, use cursors to iterate over each one and do a
count(distinct CustomerName) for each state.
There must be a more efficient way. Any suggestions?SELECT State, COUNT(CustomerName)
FROM Customers GROUP BY State
Why you need COUNT(DISTINCT)? Does your table not have a key? Is
CustomerName a person's name or a company's name?
<TravisLPruitt@.gmail.com> wrote in message
news:1141771218.305470.177870@.i39g2000cwa.googlegroups.com...
> I'm sure there is an easy solution to this, but I always end up
> resorting to using cursors, which I know is bad. Maybe somebody can
> help me bridge the gap on this one
> Assume I have a table like:
> Customers
> CustomerName nvarchar(50)
> State nvarchar(50)
> Assume I'm looking for a count of customers in each state, like:
> WA 50
> ID 24
> VA 36
> In my current solution, I select all of the distinct states into a
> temporary table, use cursors to iterate over each one and do a
> count(distinct CustomerName) for each state.
> There must be a more efficient way. Any suggestions?
>|||No, I do have a key. I was just simplifying it for the example up here.
But I think your suggestion is exactly what I needed. Thanks for the
tip!