Chapter 3: Using Simple Functions 93 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.