MySQL汇总数据&聚集函数

上一篇博客https://blog.csdn.net/m0_38061639/article/details/82854350对数据进行了计算，但我们一般不需要把数据检索出来，而是想要得到汇总结果，因此要用到聚集函数(aggregate function)。

1. AVG(): 求平均，忽略NULL值

2. COUNT(): 计数，合理运用DISTINCT

2.1

SELECT COUNT(*) FROM products;

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

| count(*) |

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

|     14 |

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

2.2

SELECT COUNT(DISTINCT vend_id) AS num_vend FROM products;

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

| num_vend |

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

|        4 |

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

3. MAX()：最大值，最小值同理

SELECT MAX(prod_price) AS max_price FROM products;

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

| max_price |

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

|     55.00 |

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

4. SUM()：求和

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num=20005;

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

| total_price |

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

|    149.87 |

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

5.组合函数，最大、最小值、平均值、求和等操作均可实现

SELECT COUNT(*) AS num_prods, MIN(prod_price) AS prod_min, MAX(prod_price) AS prod_max FROM products;

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

| num_prods | prod_min | prod_max |

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

|        14 |     2.50 |    55.00 |

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