Posted by Steve Kass on 08/23/06 01:05

In SQL Server 2005:

select
[Type],
count() over (partition by [Type]) as [Count]
from T


In SQL Server 2000:

select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T

(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)

Steve Kass
Drew University

kasterborus@yahoo.com wrote:

> My query returns a table of results, I would like to add a count column
> that contains the number of each result type returned.
>
> i.e.
>
> Type Count
> 1 3
> 1 3
> 1 3
> 2 2
> 2 2
> 3 4
> 3 4
> 3 4
> 3 4
> 4 2
> 4 2
>
> Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
>
> Is there straightforward way of doing this in SQL?
>
> Thanks
>

• Next in forum: Re: SQL 2000 dies on JOIN query
• Prev in forum: Max Row Date
• Thread view: Re: How do I get a count of each set of results?

[Reply to this message]