Grouping Results

Thus far, we have reviewed the basic use of aggregate functions, applying them to entire tables. Aggregate functions can also be used to perform analysis on groups of data. For example, instead of counting all the employees in a company, a function can count them by department. Or instead of returning the highest priced item in a table, a function can return the highest priced item in each category. This type of calculation involves grouping, or specifying how data is to be grouped). When grouped, the aggregate functions return multiple rowsone per group.

You accomplish grouping by using the GROUP BY keyword. GROUP BY takes one or more columns to be grouped (comma delimited if more than one is specified)columns that must be specified in the SELECT list.

The following example returns a list of department IDs and the number of employees in each department:

 SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id 

Joined tables can be grouped in the same way. This next example joins two tables (using a WHERE clause) to return a list of department names and the number of employees in each department:

 SELECT department_name, COUNT(*) AS num_employees FROM employees, departments WHERE employees.department_id=departments.department_id GROUP BY department_name 

Joins and join clauses were covered in Chapter 45, "Joins."

NOTE

If a SELECT statement includes a WHERE clause, you must use GROUP BY after it. Similarly, if you use an ORDER BY clause, you must use GROUP BY before it.

GROUP BY does not sort data, and although grouped columns may be sorted in ascending order, this order must never be assumed. The only guaranteed safe way to sort grouped data is to use the ORDER BY clause.