| Team Fly | |
One final example will demonstrate the grouping of multiple columns and more than one function being performed for each group. As we build on this example, we will introduce column aliases, a round function combined with an avg function and the use of a substr function, which will serve to select only a specified number of characters for the product subcategories and names results.
Let's start with the preceding group by example and build on it as we introduce some formatting and intermediate concepts. Look at the output each time and see how we are transforming it along the way. A final output listing has been provided at the end for you to compare against.
1. Start SQL*Plus and re-execute the preceding group by example:
select prod_subcategory, avg(amount_sold)
from sales s, products p
where s.prod_id = p.prod_id
and prod_category = 'Electronics'
group by prod_subcategory;
2. Add the product name to the select list. Don't forget to add it to the group by also.
select prod_subcategory, prod_name, avg(amount_sold)
from sales s, products p
where s.prod_id = p.prod_id
and prod_category = 'Electronics'
group by prod_subcategory, prod_name;
3. Rewrite the query to use a natural join, remove the table aliases and exclude the 'Home Audio' subcategory from the selection:
select prod_subcategory, prod_name, avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;
4. Add a max function calculation on the amount_sold to the query:
select prod_subcategory, prod_name, max(amount_sold), avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by prod_subcategory, prod_name;
5. Add a substr function to both the prod_subcategory and prod_name, selecting the first 18 and 25 characters, respectively, to shorten the
| Team Fly | |