GROUP BY CLAUSE

The GROUP BY clause groups rows based on one or more shared attribute values. It allows us to represent rows with identical values as a single row and is the clause largely relied upon for aggregation. Within a SELECT statement (i.e., query) the GROUP BY clause is typically accompanied by an aggregate function in the SELECT clause. Using a GROUP BY clause in conjunction with an aggregate function allows us to compute sums, minimums, maximums, and averages among other things across groups of rows. It is a cornerstone of generating data for reporting purposes.

Review the example data shown below and then let us explore the use of the GROUP BY clause to solve real-world problems.

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Solving Real-world Problems
Using the employees and orders tables above, the GROUP BY clause can be used to answer important questions, such as the following: For each job, what do employee salaries look like? Do employee salaries vary within jobs but across managers, and if so, then how much? What do our customers look like when their order histories are summarized? What do the performances of each sales representative look like? Let us explore each of these questions in turn.

For each job, what do employee salaries look like?
To answer this question, we need to be able to compute various measures for the groups of employees holding each job. The total, average, minimum, and maximum salary paid out for each job is likely a good start. Additionally, the number of employees holding a specific job and the job’s salary range is useful in instances where the average salary is skewed or may be misleading without the associated number of employee salaries it was computed across. The code below can be used to generate an output containing these measures for each job. Take a moment to review the code and corresponding output. An explanation on how the code works follows the output.

SELECT
    hr.employees.job_id,
    SUM(hr.employees.salary) AS total_salary,
    COUNT(hr.employees.employee_id) AS employee_count,
    AVG(hr.employees.salary) AS average_salary,
    MAX(hr.employees.salary) AS maximum_salary,
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) - MIN(hr.employees.salary) AS salary_range
FROM
	hr.employees
GROUP BY
    hr.employees.job_id;

Let us break down the steps involved in generating the output above by starting with a few facts regarding the employees table. The employees table contains 107 rows with each row representing a single employee. Across the 107 employees, nineteen unique jobs are held. The query above groups the employees based on their respective job ID values. The measures are subsequently computed within each group. The values returned for each group are represented within a single row along with the job ID value defining the group. To reinforce this idea, let us walk through the process manually for the group of IT programmers.

Based on the employees table shown at the beginning of this page, there are five employees with a job ID value of “IT PROG.” The ID values of those employees are 103, 104, 105, 106, and 107 and the salary values are $9,000, $6,000, $4,800, $4,800, and $4,200, respectively. Computing the total salary amount paid out within this group of employees results in a total salary value of $28,880. As expected, this value matches the value observed in the output for the total salary measure. If we computed each measure for each group of employees holding the same job, then we would arrive at the same results shown in the output above.

Before moving onto the next example, consider jotting down a few comments addressing the question, “What do employee salaries look like?” A few of my own can be found in the comments section below and will be included in each of the example problems that follow. Click the icon to see my comments.

Do employee salaries vary within jobs but across managers, and if so, then how much?
To tackle this question, a similar approach to that seen above can be taken. The same measures require computing; however, a different level of detail is required. We need to compute the values for each job ID and manager ID combination. Each of those combinations are the groups and those groups are made of the employees holding a specific job and reporting to a specific manager. For example, the financial accountants with employee ID values of 109, 110, 111, 112, and 113 reporting to the manager with an ID value of 108 are a single group. Each of the measures are computed using these five employees’ salaries. Their values are subsequently represented within a single row along with the job ID value and manager ID value defining the group (FI_ACCOUNT and 108, respectively).

SELECT
    hr.employees.job_id,
    hr.employees.manager_id,
    SUM(hr.employees.salary) AS total_salary,
    COUNT(hr.employees.employee_id) AS employee_count,
    AVG(hr.employees.salary) AS average_salary,
    MAX(hr.employees.salary) AS maximum_salary,
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) - MIN(hr.employees.salary) AS salary_range
FROM
	hr.employees
GROUP BY
    hr.employees.job_id,
    hr.employees.manager_id;

What do our customers look like when their order histories are summarized?
Understanding past customer purchasing behavior is beneficial to being able to potentially predict the purchasing behavior of customers in the future. Describing each customer’s behavior, at a high-level, can be a good start. The code below allows us to do that by answering the following key questions:

– How much has a customer spent in total across all their orders?
– How many orders has the customer placed?
– On average, how much did a customer spend per order?
– What was the customer’s largest and smallest amount spent in a single order?
– What is the difference, in terms of amount spent, between the customer’s largest and smallest order?
– When did the customer place their first order?
– When did the customer place their most recent order?

SELECT
    oe.orders.customer_id,
    SUM(oe.orders.order_total) AS total_order_total,
    COUNT(oe.orders.order_id) AS order_count,
    AVG(oe.orders.order_total) AS average_order_total,
    MAX(oe.orders.order_total) AS maximum_order_total,
    MIN(oe.orders.order_total) AS minimum_order_total,
    MAX(oe.orders.order_total) - MIN(oe.orders.order_total) AS order_total_range,
    MAX(oe.orders.order_date) AS latest_order_date,
    MIN(oe.orders.order_date) AS first_order_date
FROM
	oe.orders
GROUP BY
    oe.orders.customer_id;

In the output above, there is one row per customer. In each of those rows, the results of aggregating the customer’s orders is populated. How did this come to be? In the same manner as previous examples, the GROUP BY clause grouped the rows based on the specified attribute(s). In this case, the clause grouped rows based on the customer ID value. Subsequently, several aggregate functions were computed within each group (i.e., customer) and across the respective group’s orders.

What do the performances of each sales representative look like?
For sales representatives and those managing sales representatives, job performance is often a key factor in determining total compensation. Typically, a sales commission is awarded based on the number of sales a representative has made. Other factors may also contribute to the amount awarded. Using the code below, we can quickly summarize the performances of each sales representative. Notice, the code is largely the same as that used in the previous example. There are two differences. The first difference is the sales representative ID attribute has replaced the customer ID attribute in the SELECT and GROUP BY clauses. This is required to summarize each sales representative’s data. The second difference is the use of a WHERE clause. The WHERE clause ensures only those orders associated with a sales representative are included. This filtering operation takes place prior to the grouping operation. For clarity, see the code’s corresponding output.

SELECT
    oe.orders.sales_rep_id,
    SUM(oe.orders.order_total) AS total_order_total,
    COUNT(oe.orders.order_id) AS order_count,
    AVG(oe.orders.order_total) AS average_order_total,
    MAX(oe.orders.order_total) AS maximum_order_total,
    MIN(oe.orders.order_total) AS minimum_order_total,
    MAX(oe.orders.order_total) - MIN(oe.orders.order_total) AS order_total_range,
    MAX(oe.orders.order_date) AS latest_order_date,
    MIN(oe.orders.order_date) AS first_order_date
FROM
	oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL
GROUP BY
    oe.orders.sales_rep_id;

I won’t reiterate how the GROUP BY clause works again; however, I do want to touch on how a few of the measures computed and displayed in the output above can be useful. As previously mentioned, the sales commission awarded to each sales representative will likely be based on the number of sales made and perhaps even the total dollar amount associated with the orders. What may not be immediately apparent is the usefulness of the latest order date and first order date values. With a little extra work, the values derived can be used to compute the number of days elapsed between the beginning of a period (e.g., sales year, sales month) and the first sale made by each sales representative. Short durations could indicate a strong start while long durations could indicate a slow start. Likewise, using the latest order date values can tell us whether or not the sales representative has recently made a sale or is struggling to do so. Keep in mind, when using a non-fictional data set a WHERE clause should be used here to filter to the date period of interest unless it is desirable to look at all-time sales performance.

Missing Values
Aggregate functions, with the exception of COUNT(*), ignore NULL values. For example, a SUM computed across the values, 100, 200, 200, NULL, and 300 returns 800. Likewise, an average computed across the same same values will return 200. If rows containing NULLs for the attribute being aggregated are required to be accounted for when computing an average, then consider using the COALESCE function within the average function to replace the missing values with zero prior to the average being computed. For more information on handling missing values, see my page dedicated to this topic, here.

COUNT(*) vs. COUNT(attribute)
When using the COUNT function, it is important to understand the difference in the function’s behavior when the argument passed to the function is an attribute instead of the “*” character. When an attribute is passed to the function, the count returned represents the number of rows with non-null values for the specified attribute while COUNT(*) simply represents the number of rows returned. For clarity, see the example code and corresponding output below.

SELECT
	COUNT(*) AS order_count,
	COUNT(oe.orders.sales_rep_id) AS assisted_order_count
FROM
	oe.orders;

In the output above, COUNT(*) returns the number of rows (i.e., orders) within the orders table. When the sales representative ID attribute is passed into the COUNT function, the number of rows with a sales representative ID value populated is returned (70). This value tells us seventy orders were placed with the assistance of a sales representative.

Common Errors
For users new to SQL, the GROUP BY clause can cause a large amount of frustration. Most of this frustration is rooted in how the SELECT and GROUP BY clauses work together to produce a desired output. When the GROUP BY clause is used, any attributes not treated as constants or are non-aggregates in the SELECT clause must also be specified in the GROUP BY clause. When this is not the case, Oracle database will return the error, ORA-00979: not a GROUP BY expression. To help reinforce why Oracle and other relational database management system products return an error when the SELECT and GROUP BY clauses are not in agreement, let us discuss the code below.

-- Returns ORA-00979: not a GROUP BY expression.
SELECT
    hr.employees.job_id,
    hr.employees.manager_id,
    COUNT(hr.employees.employee_id) AS employee_count
FROM
    hr.employees
GROUP BY
    hr.employees.job_id;

The GROUP BY clause specified states to group the rows based on the unique job ID values. To reiterate, this specification states to return one row per job ID value. This is problematic because two attributes, job ID and manager ID, are specified in the SELECT clause. The SELECT clause is processed after the GROUP BY clause, so by the time the SELECT clause is being processed for this query, the rows have already been grouped by job ID values. As a result, the level of detail in SELECT clause is not aligned with the GROUP BY clause. This is easy to see for any job ID value associated with more than one manager. With only one row returned for each job ID value, there are not enough rows to also account for each manager ID and job ID combination. The employee count computation also presents an issue in that it is computed at the job ID level. This does not make sense given the attributes in the SELECT clause.

If you are learning the clauses in the typical order and as displayed in the menus, then your next stop is the HAVING clause page. This clause allows users to perform filtering operations on grouped rows. For more information, see the HAVING clause page.

Knowledge Check
If you’d like to confirm your understanding on the GROUP BY clause and other fundamental concepts, consider solving a handful of my practice problems here.