group by Clauses and Views

When creating a view for security reasons, be careful when using aggregate functions and the group by clause.

A Transact-SQL extension allows you to name columns that do not appear in the group by clause. If you name a column that is not in the group by clause, the SAP ASE server returns detailed data rows for the column. For example, this Transact- SQL extended column query returns a row for every 18 rows—more data than you might intend:

select title_id, type, sum (total_sales)
from titles
group by type

While this ANSI-compliant query returns one row for each type (6 rows):

select type, sum (total_sales)
from titles
group by type