How do I get a count of each set of results?

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?

ThanksIn 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:

Quote:

Originally Posted by

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
>