MySQL learning footprint record 10 -- Summary Data -- MAX (), MIN (), AVG (), SUM (), COUNT ()

MySQL learning footprint record 10 -- Summary Data -- data used in this article, such as MAX (), MIN (), AVG (), SUM (), and COUNT ()

mysql> SELECT prod_price FROM products;+------------+| prod_price |+------------+|       5.99 ||       9.99 ||      14.99 ||      13.00 ||      10.00 ||       2.50 ||       3.42 ||      35.00 ||      55.00 ||       8.99 ||      50.00 ||       4.49 ||       2.50 ||      10.00 |+------------+14 rows in set (0.00 sec)

 

1. aggregate Function AVG (): returns the average COUNT () of a column: returns the number of rows in a column MAX (): returns the maximum MIN () of a column (): returns the SUM () of the minimum values of a column: returns the 2.AVG() function that returns the SUM of values of a column.

Examples: mysql> select avg (prod_price) AS avg_price-> FROM products; + ----------- + | avg_price | + ----------- + | 16.133571 | + ----------- + 1 row in set (0.01 sec) * return the average value of a specific column or row Examples: mysql> select avg (prod_price) AS avg_price # filter out products with vend_id 1003, then calculate the average value-> FROM products-> WHERE vend_id = 1003; + ----------- + | avg_price | + ----------- + | 13.212857 | + ----------- + 1 row in set (0.00 sec)

 

Tips: AVG () can only be used to calculate the average value of a specific value column. To obtain the average value of multiple columns, you must use multiple AVG () functions AVG () the function ignores the rows whose column value is NULL. the COUNT () function * COUNT (*) is used to COUNT the number of rows in a table, regardless of whether the column label contains a NULL value or a non-NULL value * COUNT (column) counts rows with values in a specific column and ignores NULL values.

Examples: mysql> select COUNT (*) AS count_prod from products; + ------------ + # count the number of rows in the products table | count_prod | + ------------ + | 14 | + ------------ + 1 row in set (0.00 sec) first, list the content of cust_email mysql> SELECT cust_email FROM MERs MERS; + --------------------- + | cust_email | + --------------------- + | ylee@coyote.com | NULL | rabbit@wascally.com | sam@yosemite.com | NULL | + ------------------- + 5 rows in set (0.00 sec) COUNT cust_email mysql> select count (cust_email) AS num_cust-> FROM MERs MERS; # ignore NULL value + ---------- + | num_cust | + ---------- + | 3 | + ---------- + 1 row in set (0.00 sec)

 

4. the MAX () function returns the maximum value in the specified column, ignoring the NULL value.

Examples: mysql> SELECT MAX(prod_price) AS max_price          -> FROM products;+-----------+| max_price |+-----------+|     55.00 |+-----------+1 row in set (0.00 sec)

 

5. The MIN () function * returns the minimum value of the specified column.

mysql> SELECT MIN(prod_price) AS min_price         -> FROM products;+-----------+| min_price |+-----------+|      2.50 |+-----------+1 row in set (0.00 sec)

 

6. The SUM () function * returns the SUM of the specified column values.

mysql> SELECT SUM(prod_price) AS sum_price          -> FROM products;+-----------+| sum_price |+-----------+|    225.87 |+-----------+1 row in set (0.00 sec)

 

* SUM can also be used to calculate the total value Examples: The following lists the data to be calculated first.

Mysql> SELECT item_price, quantity-> FROM orderitems-> WHERE order_num = 20005; + ------------ + ---------- + | item_price | quantity | + ------------ + ---------- + | 5.99 | 10 | 9.99 | 3 | 10.00 | 5 | 10.00 | 1 | + ------------ + ---------- + 4 rows in set (0.01 sec) mysql> select sum (item_price * quantity) AS total_price-> FROM orderitems # Return the SUM of the prices of all items in the order-> WHERE order_num = 20005; + ------------- + | total_price | + ------------- + | 149.87 | + ------------- + 1 row in set (0.00 sec)

 

7. aggregates different values. For SUM (), MAX (), MIN (), AVG (), COUNT (), the default parameter DISTINCT is ALL, to calculate a value that only contains different values, you must specify the DISTINCT parameter.

 EXAMPLES:   mysql> SELECT AVG(DISTINCT prod_price) AS avg_price            -> FROM products            -> WHERE vend_id = 1003;+-----------+| avg_price |+-----------+| 15.998000 |+-----------+1 row in set (0.02 sec)

 

8. Aggregate functions

  eg:   mysql> SELECT COUNT(*) AS num_items,            -> MIN(prod_price) AS price_min,           -> MAX(prod_price) AS price_min,           -> AVG(prod_price) AS price_avg           -> FROM products;+-----------+-----------+-----------+-----------+| num_items | price_min | price_min | price_avg |+-----------+-----------+-----------+-----------+|        14 |      2.50 |     55.00 | 16.133571 |+-----------+-----------+-----------+-----------+1 row in set (0.00 sec)