MySQL Aggregate Functions

In some cases, the information we need is not actually stored in the database but we can retrieve it by computing in some ways from stored data. For example, we have OrderDetails table to store order data, we don't know total of money of all selling products when we look at the table. In order to do so, we can use aggregate functions. By definition, aggregate functions allow us to perform a calculation on a set of records and return a single returned value. Aggregate functions ignore null value when performing calculation except COUNT function. Aggregate functions are often used with GROUP BY clause of SELECT statement. Here are aggregate functions which MySQL supports:

SUM Function

SUM function returns the sum of all values in an expression.

Let's practice with OrderDetails table by following examples:

To get the total money for each selling product we just use the SUM function and group by product. Here is the query:

SELECT productCode,sum(priceEach * quantityOrdered) total
FROM orderdetails
GROUP by productCode

productCode    total 
-----------  ---------
S10_1678      90157.77
S10_1949     190017.96
S10_2016     109998.82
S10_4698        170686
S10_4757     127924.32
S10_4962     123123.01
S12_1099     161531.48

To see the result more details, we can join the OrderDetails table with Product table.

SELECT P.productCode,
      P.productName,
      SUM(priceEach * quantityOrdered) total
FROM orderdetails O
INNER JOIN products  P ON O.productCode = P.productCode
GROUP by productCode
ORDER BY total

productCode  productName                                    total 
-----------  -------------------------------------------  ---------
S24_1937     1939 Chevrolet Deluxe Coupe                   28052.94
S24_3969     1936 Mercedes Benz 500k Roadster              29763.39
S24_2972     1982 Lamborghini Diablo                       30972.87
S24_2840     1958 Chevy Corvette Limited Edition           31627.96
S32_2206     1982 Ducati 996 R                             33268.76
S24_2022     1938 Cadillac V-16 Presidential Limousine     38449.09
S50_1341     1930 Buick Marquette Phaeton                  41599.24
S24_1628     1966 Shelby Cobra 427 S/C                     42015.54
S72_1253     Boeing X-32A JSF                              42692.53

AVG Function

AVG is used to calculate average value of an expression. It ignores NULL values.

AVG(expression)

We can use AVG function to calculate the average price of all products buy executing the following query.

SELECT AVG(buyPrice) average_buy_price
FROM Products

Here is the result

average_buy_price
-----------------
 54.395181818182

MAX and MIN Function

MAX function returns the maximum and MIN function returns the minimum value of the set of values in expression.

MAX(expression)

MIN(expression)

As an example, we can use MIN and MAX function to retrieve the highest and lowest price product as follows:

SELECT MAX(buyPrice) highest_price,
      MIN(buyPrice) lowest_price
FROM Products

You will get the result

highest_price  lowest_price
-------------  ------------
      103.42         15.91

COUNT Function

COUNT function returns the count of the items in expression. We can use COUNT function to count how many products we have as follows:

SELECT COUNT(*) AS Total
FROM products

Total
------
  110

For more information on count function check it out here