Using the GROUP BY Clause

You use the optional GROUP BY clause to organize data retrieved from aggregate functions. When you issue a query (a SELECT statement) that has both aggregate (AVG, COUNT, MIN, MAX, or SUM) and non-aggregate columns, you must use GROUP BY to group the result set by each of the nonaggregate columns. The three following rules apply:

• Each column from which you are doing a nonaggregate SELECT must appear in the GROUP BY clause
• The GROUP BY clause can reference only columns that appear in the SELECT clause
• Each SELECT clause in a query can have only one GROUP BY clause

A group is defined as the subset of rows that match a distinct value in the columns of the GROUP BY clause.

Grouping the Result Set of Aggregate Functions

1. Execute the following query to find out how many employees there are in each country:

SELECT COUNT(emp_no), job_country FROM Employee

GROUP BY job_country

The result set should look like this:

Advance To:

Using the HAVING Clause