| Team Fly | |
The entire Electronics category was treated as one group. If we wanted to see the average amount sold for each subcategory within the Electronics category, we will need to use a group by clause in our query, lumping each of the Electronics subcategories together before calculating the average. Each group by clause is accomplished by putting the column or columns to group by in the select list followed by one or more functions. A group by statement follows the where clause, and it must include each of the select list columns that are not acted upon by a group function. Let's take a look at an example:
SQL> select prod_subcategory, avg(amount_sold) 2 from sales s, products p 3 where s.prod_id = p.prod_id 4 and prod_category = 'Electronics' 5 group by prod_subcategory; PROD_SUBCATEGORY AVG(AMOUNT_SOLD) -------------------------------------------------- ---------------- Game Consoles 300.523928 Home Audio 582.175922 Y Box Accessories 18.7803303 Y Box Games 22.640670
This group by example illustrates a column and function in the select list and the repetition of the column again in the group by clause.
Just as you have used selection criteria to reduce the result set, you can apply the having clause to summarized data from a group by operation to restrict the groups returned. Using the previous example, suppose you only wanted to see the Product Subcategory groups that had an average amount sold greater than 300. The following is a having clause executed against the avg(amount_sold) aggregation example:
SQL> select prod_subcategory, avg(amount_sold) 2 from sales s, products p 3 where s.prod_id = p.prod_id 4 and prod_category = 'Electronics' 5 group by prod_subcategory 6 having avg(amount_sold) > 300; PROD_SUBCATEGORY AVG(AMOUNT_SOLD) -------------------------------------------------- ---------------- Game Consoles 300.523928 Home Audio 582.175922
| Team Fly | |