Group By and Having

The group by clause is used to group records by an attribute/set of attributes. An aggregate operation (count, max, min, avg etc.) can be performed after grouping.

select * from r group by attribute

Note that all the attributes in the select clause (that aren't part of an aggregate function) must be present in the group by clause.

select dept_name, ID, avg (salary)
from instructor
group by dept_name;

is an erroneous query because ID isn't in the group by clause (salary can be exempted because it is part of the avg aggregate function).

The having clause is a predicate for grouping.

Note that the where clause is applied before grouping and the having clause is applied after grouping.

select * from r where P1 group by attribute having P2