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_idGROUP BY category_nameORDER BY category_name; Output: USE bike: • Set the bike database to be the default SELECT category_name, CONCAT('$', ROUND(AVG(list_price),2)) AS 'Average List Price'

• Return the category_name from the category table.
• You do not have to qualify the column name with the table name because category_name only exists in one table of the join.
• Return the list price with the ‘\$’ followed by the list_price rounded to the 2nd decimal and assigned a column alias of ‘Average List Price’.
• You do not have to qualify the column name of list_price because it exists in only one table of the join.

FROM product p

JOIN category c

ON p.category_id = c.category_id

• JOIN the product table to the category table
• Assign a table alias of “p” to product and “c” to category
• The join condition is the primary key of category_id from the category table equal to the foreign key of category_id in the product table.

GROUP BY category_name

• Instead of retrieving a single value with the average price of all products, return a list of average prices by category name.

ORDER BY category_name;

• Sort the results by category_name

CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.

### End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
1. Very Low Quality
2. Low Quality
3. Moderate Quality
4. High Quality
5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.