Get MySQL aggregate functions in one article

In general, the aggregate data we need (sum, average, maximum and minimum values, etc.) is not always stored in the table. But it can be obtained by performing calculations on stored data.

For example, because the orderDetails table only stores the quantity and price of each item, the total amount of each order cannot be obtained by directly querying the orderdetails table. The quantity and price of the item must be queried for each order, and the total amount of the order must be calculated.
To perform such calculations in a query, it is necessary to use aggregate functions.

Aggregate functions perform calculations on a set of values ​​and return a single value.

MySQL provides many aggregate functions, including AVG, COUNT, SUM, MIN, MAX, etc.
Except for the COUNT function, other aggregate functions ignore NULL values ​​when performing calculations.

AVG

Calculate the average of a set of values.

AVG(expression)

You can use the AVG() function to calculate the average price of all products in the products table by using the following query:

mysql> SELECT AVG(buyPrice) average_buy_price
FROM products;
+-------------------+
| average_buy_price |
+-------------------+
| 54.395182         |
+-------------------+
1 row in set

COUNT

Returns the number of rows in the table.
For example, get the number of products in the products table:

mysql> SELECT COUNT(*) AS Total
FROM products;
+-------+
| Total |
+-------+
|   110 |
+-------+
1 row in set

The COUNT() function has several forms, such as COUNT(*) and COUNT(DISTINCT expression)

SUM()

Returns the sum of a set of values. If no matching row is found, the SUM() function returns a NULL value.

For example, to get the total sales volume of each product, with GROUP BY:

mysql> SELECT productCode,sum(priceEach * quantityOrdered) total
FROM orderdetails
GROUP by productCode;
+-------------+-----------+
| productCode | total     |
+-------------+-----------+
| S10_1678    | 90157.77  |
| S700_3505   | 84992.25  |
****** 此处省略了一大波数据 ********
| S700_3962   | 78919.06  |
| S700_4002   | 71753.93  |
| S72_1253    | 42692.53  |
| S72_3212    | 47550.40  |
+-------------+-----------+
109 rows in set

To view the results in more detail, you can connect the orderdetails table to the products 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;
SQL
执行上面查询语句,得到以下结果 - 
mysql> 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  |
****** 此处省略了一大波数据 ************************************************
| S12_3891    | 1969 Ford Falcon                            | 152543.02 |
| S12_1099    | 1968 Ford Mustang                           | 161531.48 |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike        | 170686.00 |
| S10_1949    | 1952 Alpine Renault 1300                    | 190017.96 |
| S12_1108    | 2001 Ferrari Enzo                           | 190755.86 |
| S18_3232    | 1992 Ferrari 360 Spider red                 | 276839.98 |
+-------------+---------------------------------------------+-----------+
109 rows in set

MAX()

Returns the maximum value in a set of values.

MAX(expression)

For example, get the most expensive product in the products table

mysql> SELECT MAX(buyPrice) highest_price FROM products;
+---------------+
| highest_price |
+---------------+
| 103.42        |
+---------------+
1 row in set

MIN()

Returns the smallest value in a set of values

MIN(expression)

For example, find the lowest price product in the products table:

mysql> SELECT MIN(buyPrice) lowest_price FROM Products;
+--------------+
| lowest_price |
+--------------+
| 15.91        |
+--------------+
1 row in set