MySQL statistics function and GROUP by

Five statistical functions (used alone, with little meaning, often used in combination with grouped group by)

Max Max select Max (shop_price) from goods;

Min min Select min (shop_price) from goods;

Sum sum select SUM (shop_price) from goods;

AVG Averaging select AVG (shop_price) from goods;

Count all the rows that are worth the number of rows

COUNT (*) absolute number of rows null also counted

In addition to count (column name), count the number of non-null rows in this column

Count uses

Mysql> select * from Test8;

+------+------+

| ID | name |

+------+------+

| 1 | Lisi |

| 2 | NULL |

+------+------+

Mysql> Select COUNT (*) from TEST8;

+----------+

| COUNT (*) |

+----------+

| 2 |

+----------+

Mysql> Select COUNT (name) from Test8;

+-------------+

| COUNT (name) |

+-------------+

| 1 |

+-------------+

Inventory with a query type of 4

Select SUM (goods_number) from goods where cat_id=4;

GROUP BY

Count the inventory under each type of grouping

Mysql> Select Cat_id,sum (goods_number) from goods group by CAT_ID;

+--------+-------------------+

| cat_id | SUM (goods_number) |

+--------+-------------------+

|                 2 | 0 |

|               3 | 203 |

|                 4 | 4 |

|                 5 | 8 |

|                8 | 61 |

|                11 | 23 |

|                 13 | 4 |

|                 14 | 9 |

|                 15 | 2 |

+--------+-------------------+

Not a standard SQL statement, logically unexplained (there are many goods_name in each category cat_id)

Select Goods_name is not recommended, sum (Goods_number) from goods group by CAT_ID;

Explanation: There must be no problem with the group by a/b/c semantics in select a/ b

Tip: Query statement understanding from the back of the conditional filtering start, first understand the filter conditions, and then see the previous execution

This article is from the "Code Easy" blog, please be sure to keep this source http://codeyi.blog.51cto.com/11082384/1732454

MySQL statistics function and GROUP by