| Team Fly | |
displayed results. Don't forget to change the group by at the same time.
select substr(prod_subcategory,1,18),
substr(prod_name,1,25),
max(amount_sold),
avg(amount_sold)
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
6. Add a round function to the avg(amount_sold) function. In this step, let's also give the column names aliases to make the results more readable:
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold) Max_Amt_Sold,
round(avg(amount_sold),2) Avg_Amt_Sold
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
7. Add a having clause to return aggregated rows that have both a maximum amount sold and an average amount sold greater than 10. As one final measure, let's also add an order by:
select substr(prod_subcategory,1,18) Subcategory,
substr(prod_name,1,25) Product_Name,
max(amount_sold) Max_Amt_Sold,
round(avg(amount_sold),2) Avg_Amt_Sold
from sales natural join products
where prod_category = 'Electronics'
and prod_subcategory != 'Home Audio'
group by substr(prod_subcategory,1,18),
substr(prod_name,1,25)
having max(amount_sold) > 10
and avg(amount_sold) > 10
order by substr(prod_subcategory,1,18),
substr(prod_name,1,25);
8. Your final output should look like this:
SUBCATEGORY PRODUCT_NAME MAX_AMT_SOLD AVG_AMT_SOLD
------------------ ------------------------ ------------ ------------
Game Consoles Y Box 326.39 300.52
Y Box Accessories Xtend Memory 29.8 24.15
Y Box Games Adventures with Numbers 17.03 13.78
| Team Fly | |