How to use Aggregate functio without the Group BY clause in SQL Server 2005 and above version.

it is hard to execute a script like the following

select *, count(CategoryID) as CategoryIDCount from Products

here is the Error Message

"Msg 8120, Level 16, State 1, Line 1
Column 'Products.CategoryID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

it is quite chanllenge to put entire table columns into the group by clause.

if you are running on SQL Server 2005 or above. it is very easy solve this issue to use
 clause OVER.


select *, count(CategoryID) Over() as CategoryIDCount from Products

the output is shown below