View Definition with an Aggregate or Built-In Function

You can use a definition that includes an aggregate or built-in function must include column names in the create clause.

For example:

create view categories1 (category, average_price) 
as select type, avg(price) 
from titles 
group by type

If you create a view for security reasons, be careful when using aggregate functions and the group by clause. The Transact-SQL extension that does not restrict the columns you can include in the select with group by may also cause the view to return more information than required. For example:

create view categories2 (category, average_price) 
as select type, avg(price) 
from titles 
where type = "business"

You may have wanted the view to restrict its results to “business” categories, but the results have information about other categories.