Grouping Rows

Sometimes you might want to group blocks of rows in a table and get some information on those groups of rows. For example, you might want to get the average price for the different types of products in the products table. I ll show you how to do this the hard way, then I ll show you the easy way that involves using the GROUP BY clause to group similar rows together.

To do it the hard way, you limit the rows passed to the AVG() function using a WHERE clause. For example, the following query gets the average price for books from the products table (books have a product_type_id of 1):

  SELECT AVG(price)   FROM products   WHERE product_type_id = 1;  AVG(PRICE) ----------   24.975 

To get the average price for the other types of products, you would need to perform additional queries with different values for the product_type_id in the WHERE clause. This is all very labor intensive ! You ll be glad to know there s an easier way to do this through the use of the GROUP BY clause.

Using the GROUP BY Clause to Group Rows

You use the GROUP BY clause to group rows into blocks with a common column value. For example, the following query groups the rows from the products table into blocks with the same product_type_id :

  SELECT product_type_id   FROM products   GROUP BY product_type_id;  PRODUCT_TYPE_ID ---------------      1      2      3     4 

Notice there s one returned row in the result set for each block of rows with the same product_ type_id. For example, there s one row for products with a product_type_id of 1, another for products with a product_type_id of 2, and so on. There are actually two rows in the products table with a product_type_id of 1, and four rows with a product_type_id of 2. These rows are grouped together into separate blocks, one block for each product_type_id . The first block contains two rows, the second contains four rows, and so on.

Using Multiple Columns in a Group

You can specify multiple columns in a GROUP BY clause. For example, the following query includes the product_id and customer_id columns from the purchases table in a GROUP BY clause:

  SELECT product_id, customer_id   FROM purchases   GROUP BY product_id, customer_id;  PRODUCT_ID CUSTOMER_ID ---------- -----------    1    1    1    2     1    3    1    4    2    1    2     2    2   3    2   4     3    3 

Using Groups of Rows with Aggregate Functions

You can use blocks of rows to an aggregate function. The aggregate function performs its computation on the group of rows in each block and returns one value per block. For example, to get the average price for the different types of products in the products table, you:

• Use the GROUP BY clause to group rows into blocks with the same product_type_id .

• Use the AVG() function to get the average price for each block containing a group of rows.

The following query shows the use of the GROUP BY clause and AVG() function:

  SELECT product_type_id, AVG(price)   FROM products   GROUP BY product_type_id;  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------       1 24.975      2   26.22       3   13.24      4   13.99         13.49 

Notice there are five rows in this output, with each row corresponding to one or more rows in the products table grouped together with the same product_type_id column value. There are two rows in the products table with a product_type_id of 1. These two rows have been grouped together and passed to the AVG() function in the previous query. AVG() computes and returns the average price for the two rows, which is 24.975 as shown in the first row of the previous result set. Similarly, there are four rows with a product_type_id of 2, with an average price of 26.22 as shown in the second row of the result set.

Notice the last row of output has a null value for the product_type_id . This final group consists of the single row with the null product_type_id at the end of the products table. If there was another row in the products table with a null product_type_id , that row would also be placed in the final group. That row s price column value would have also been used in the average computation.

You can use any of the aggregate functions with the GROUP BY clause. For example, the following query gets the number of rows in each product_type_id group using the COUNT() function:

  SELECT product_type_id, COUNT(product_id)   FROM products   GROUP BY product_type_id;  PRODUCT_TYPE_ID COUNT(PRODUCT_ID) --------------- -----------------      1      2       2     4     3    2       4     3          1 

Notice I used the product_id column in the COUNT() function in this query, rather than product_type_id . This is because product_type_id is null for product #12, and would be ignored by the COUNT() function, as shown in the following example, which displays 0 for COUNT(product_type_id) in the last row of the result set:

  SELECT product_type_id, COUNT(product_type_id)   FROM products   GROUP BY product_type_id;  PRODUCT_TYPE_ID COUNT(PRODUCT_TYPE_ID) --------------- ----------------------     1        2       2         4     3        2     4      3              0 

Using the ORDER BY Clause to Sort Groups

By default, GROUP BY sorts the rows in ascending order based on the values in the group column. For example, in the previous query the rows were sorted on the product_type_id column. You can change the column used in the sort using the ORDER BY clause, which you saw in the previous chapter. For example, the following query sorts the output using ORDER BY COUNT (product_type_id) :

  SELECT product_type_id, COUNT(product_type_id)   FROM products   GROUP BY product_type_id   ORDER BY COUNT(product_type_id);  PRODUCT_TYPE_ID COUNT(PRODUCT_TYPE_ID) --------------- ----------------------               0      1         2       3      2      4         3     2       4 

One point to note is that you don t have to include the columns used in the GROUP BY clause in your SELECT clause. For example, the following query is the same as the previous example except product_type_id is omitted from the SELECT clause:

  SELECT COUNT(product_id)   FROM products   GROUP BY product_type_id   ORDER BY COUNT(price);  COUNT(PRODUCT_ID) -----------------       1        2        2        3       4 

Incorrect Usage of Aggregate Function Calls

When your query contains an aggregate function ”and selects columns not placed within an aggregate function ”those columns must be placed in a GROUP BY clause. If you forget to do this, you ll get the following error: ORA-00937: not a single-group group function . For example, the following query selects the product_type_id column and AVG(price) but omits a GROUP BY clause for product_type_id :

 SQL>  SELECT product_type_id, AVG(price)  2  FROM products;  SELECT product_type_id, AVG(price)    * ERROR at line 1: ORA-00937: not a single-group group function 

The error occurs because the database doesn t know what to do with the product_type_id column in the result set. Think about it: the query attempts to use the AVG() aggregate function that operates on multiple rows, but the query also attempts to get the product_type_id column values for each individual row. You can t have both at the same time. You must provide a GROUP BY clause to tell the database to group multiple rows with the same product_type_id column value together. The database will then pass those groups of rows to the AVG() function.

Caution  

When your query contains an aggregate function ”and selects columns not placed within an aggregate function ”those columns must be placed in a GROUP BY clause.

Also, you cannot use an aggregate function to limit rows in a WHERE clause. If you try to do so you will get the following error: ORA-00934: group function is not allowed here . For example:

 SQL>  SELECT product_type_id, AVG(price)  2  FROM products  3  WHERE AVG(price)  >  20  4  GROUP BY product_type_id;  WHERE AVG(price) > 20   * ERROR at line 3: ORA-00934: group function is not allowed here 

The error occurs because you may only use the WHERE clause to filter individual rows ”and not groups of rows. To filter groups of rows you use the HAVING clause, which you ll learn about in the next section.

Using the HAVING Clause to Filter Groups of Rows

You use the HAVING clause to filter groups of rows. You place the HAVING clause after your GROUP BY clause:

 SELECT ... FROM ... WHERE GROUP BY ... HAVING ... ORDER BY ...; 

Note  

GROUP BY can be used without HAVING , but HAVING must be used in conjunction with GROUP BY .

Let s take a look at an example. Say you want to view the types of products that have an average price greater than $20. To do this, you

• Use the GROUP BY clause to group rows into blocks with the same product_type_id .

• Use the HAVING clause to limit the returned results to those groups that have an average price greater than $20.

The following query shows the use of these GROUP BY and HAVING clauses:

  SELECT product_type_id, AVG(price)   FROM products   GROUP BY product_type_id   HAVING AVG(price)  >  20;  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------     1  24.975      2  26.22 

As you can see, only the groups of rows having an average price greater than $20 are displayed.

Using the WHERE and GROUP BY Clauses Together

You can use the WHERE and GROUP BY clauses together in the same query. When you do this, the WHERE clause first filters the rows returned, then the remaining rows are grouped into blocks by the GROUP BY clause. For example, the following query uses

• A WHERE clause to filter the rows from the products table to those whose price is less than $15

• A GROUP BY clause to group the remaining rows by the product_type_id column

    SELECT product_type_id, AVG(price)   FROM products   WHERE price  <  15   GROUP BY product_type_id;  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------     2  14.45      3   13.24     4   12.99          13.49 

Using the WHERE, GROUP BY, and HAVING Clauses Together

You can use the WHERE , GROUP BY , and HAVING clauses together in the same query. When you do this, the WHERE clause first filters the rows, then the remaining rows are grouped into blocks by the GROUP BY clause, and finally the row groups are filtered by the HAVING clause. For example, the following query uses

• A WHERE clause to filter the rows from the products table to those whose price is less than $15

• A GROUP BY clause to group the remaining rows by the product_type_id column

• A HAVING clause to filter the row groups to those whose average price is greater than $13

    SELECT product_type_id, AVG(price)   FROM products   WHERE price  <  15   GROUP BY product_type_id   HAVING AVG(price)  >  13;  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------       2   14.45      3   13.24         13.49 

Compare these results with the previous example. Notice that the group of rows with the product_type_id of 4 is filtered out. That s because the group of rows has an average price less than $13.

The final example adds an ORDER BY clause to the previous example to order the results by the average price:

  SELECT product_type_id, AVG(price)   FROM products   WHERE price  <  15   GROUP BY product_type_id   HAVING AVG(price)  >  13   ORDER BY AVG(price);  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------      3 13.24        13.49     2  14.45