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

Source: Internet
Author: User

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)

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.