mysql使用聚合函数查询

用来对数据进行总结

图片.png

1、COUNT()函数

• COUNT(*)计算表中总的行数，不管某列有数值或者为空值。
• COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
  SELECT COUNT(*) AS cust_num FROM tb_name;
SELECT COUNT(c_email) AS email_num FROM tb_name;

2、SUM()函数
求总和的函数，返回指定列值的总和。
SELECT SUM(quantity) AS sum FROM tb_name WHERE o_num=30005;
计算每个分组的和：
SELECT o_num,SUM(quantity) AS items_total FROM tb_name ORDER BY o_num;

3、AVG函数
通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
SELECT AVG(f_price) AS avg_price FROM tb_name WHERE s_id=103;
SELECT AVG(f_price) AS avg_price FROM tb_name GROUT BY s_id;

4、MAX()函数
SELECT s_id,MAX(f_price) AS max_price FROM tb_name GROUP BY s_id;

5、MIN()函数
SELECT s_id,MIN(f_price) AS min_price FROM tb_name GROUP BY s_id;