Aggregate Functions (SUM, COUNT, AVG, MIN, and MAX) and the GROUP BY Clause

MySQL provides functions that return single calculated values based on defined sets of values. We'll first look at the general way to use these functions in a SQL query, and then we'll examine each function individually:

• SUM: Adds, or sums, relevant values
• COUNT: Returns a count of the relevant values
• AVG (average): Calculates the average of a set of relevant values
• MIN (minimum value): Returns the lowest value of a set of values
• MAX (maximum value): Returns the highest value of a set of values

Say that a store manager wants the average weight of the products in each PACKT_ONLINE_SHOP product category. In the PACKT_ONLINE_SHOP database, each ProductCategory table row maps to one or more products in the Products table. We can start with this query:

USE     packt_online_shop;
SELECT  PC.ProductCategoryID, PC.ProductCategoryName, P.UnitKGWeight         AS 'PRODUCT...