Whenever you need aggregated values from a subset of one table when the subset is not build because of the value of one or several attributes (in other words when you didn't use "where" in the select) or of course when the resultset is build of a query over several tables

I think:

1. When you need multi-level filtering of a table

2. When you need a value from several tables

you can use Group By Clause

For example above same query you can execute like:

SELECT AVG(salary),department

FROM emp

GROUP BY department.

Try It.