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

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

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

Data used in this article

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 functions

AVG (): returns the average value of a column.

COUNT (): returns the number of rows in a column.

MAX (): returns the maximum value of a column.

MIN (): returns the minimum value of a column.

SUM (): returns the SUM of values of a column.

2. AVG () function

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, multiple AVG () functions must be used.

The AVG () function ignores rows whose column value is NULL.

3. COUNT () function

* COUNT (*) indicates the number of rows in the table, regardless of whether the column label contains NULL or non-NULL values.

* 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; + --------------------- + | 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; # ignore NULL value + ---------- + | num_cust | + ---------- + | 3 | + ---------- + 1 row in set (0.00 sec)

4. 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. MIN () function

* Returns the minimum value of a specified column.

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

6. 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.

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. aggregate different values with the keyword DISTINCT

For SUM (), MAX (), MIN (), AVG (), COUNT (), the default parameter 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)

BitsCN.com