The window function brings the summary data into the detail row, but does not affect how many records are returned.

If you can't use a group by because you are mixing partitions, you can use a distinct clause.

These two queries return identical results, but the group by is more efficient.

SELECT TypeCount = COUNT(*) ,

type

FROM sys.objects

GROUP BY type

ORDER BY type;

SELECT DISTINCT

TypeCount = COUNT(*) OVER ( PARTITION BY type ) ,

type

FROM sys.objects AS o

ORDER BY type;