Group By And The Select Clause

In MS SQL Server, when you use a ‘Group By’ clause, every field in the ‘Select’ clause either needs to be present in the ‘Group By’ clause, or it needs to be aggregated (i.e. a function like Sum, Avg, Min, Max or Count needs to be performed on this column).

ASE does not have such a restriction.

Example

create table Example (Id int, Amount int, PayedBy varchar(30))

insert into Example values (1, 1, 'Jack')

insert into Example values (1, 10, 'Jill')

insert into Example values (1, 5, 'Jack')

insert into Example values (2, 2, 'Jack')

insert into Example values (2, 20, 'Jill')

insert into Example values (2, 6, 'Jack')

select Id, Sum(Amount) from Example group by Id

-- This statement will not work on MS SQL Server

select Id, PayedBy, Sum(Amount) from Example group by Id

drop table Example

Note: Although ASE seems to offer you more flexibility, the actual result of the query ‘select Id, PayedBy, Sum(Amount) from Example group by Id’ is somewhat unexpected. For every record in the table, there will be a record in the result set. The aggregate columns will have the correct value according to the ‘Group By’ clause, but this value is repeated for every record in the group.