5.4

Improving the GROUP BY Query

Improving the GROUP BY Query

  • The report would be nicer if we showed the category name instead of the category_id. This will require joining the product table to the category table.
  • We can ROUND the AVG list price by category to TWO decimals points.
  • We can CONCAT the dollar sign to the left of the list_price.

Code Sample:

USE bike;
SELECT category_name, 
    CONCAT('$', ROUND(AVG(list_price),2)) AS 'Average List Price'
FROM product p
    JOIN category c
    ON p.category_id = c.category_id
GROUP BY category_name
ORDER BY category_name;

Output:

ag_03.png

USE bike:

SELECT category_name,

     CONCAT('$', ROUND(AVG(list_price),2)) AS 'Average List Price'

FROM product p

     JOIN category c

    ON p.category_id = c.category_id

GROUP BY category_name

ORDER BY category_name;

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/learning_mysql/improving_the_group_.