A common error with groups is to try to get information which cannot properly be put in a group. For example,
SELECT sales_rep, emp_lname, count( * ) FROM sales_order KEY JOIN employee GROUP BY sales_rep
gives the error
column ‘emp_lname’ cannot be used unless it is in a GROUP BY.
SQL does not realize that each of the rows for an employee with a given ID have the same value of emp_lname. An error is reported since SQL does not know which of the names to display.
However, the following is valid:
SELECT sales_rep, max( emp_lname ), count( * ) FROM sales_order KEY JOIN employee GROUP BY sales_rep
The max function chooses the maximum (last alphabetically) surname from the detail rows for each group. The surname is the same on every detail row within a group so the max is just a trick to bypass a limitation of SQL.