aggregate function

表结构

CREATE TABLE products (
prod_id char(10) NOT NULL,
vend_id int NOT NULL,
prod_name char(255) NOT NULL,
prod_price decimal(8,2) NOT NULL,
prod_desc text,
PRIMARY KEY (prod_id),
KEY fk_products_vendors (vend_id),
CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

表数据

SELECT * FROM products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |
| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |
| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |
| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |
| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |
| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |
| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |
| OL1 | 1002 | Oil can | 8.99 | Oil can, red |
| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |
| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |
+---------+---------+----------------+------------+----------------------------------------------------------------+

AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

AVG()

使用AVG()返回products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products

用AVG来计算特定列（几列）的平均值
SELECT AVG(prod_price) AS avg_1003_price FROM products WHERE vend_id=1003;

COUNT()

COUNT函数进行计数，可利用COUNT()确定表中行的数目或符合特定条件的行的数目
COUNT函数有两种使用方式：

1. 使用COUNT(*）对表中行的数目进行计数，不管表列中包含的是空值还是非空值

•  SELECT COUNT(*) FROM products
  

2.使用COUNT（columns）对特定列中具有的值的行进行计数，忽略NULL值

•  SELECT COUNT(cust_mail) AS num_cust FROM products;
  

MAX()

MAX()返回指定列中的最大值，max()要求指定列名

• SELECT MAX(prod_price) AS max_price FROM products

MIN()

MIN()返回指定列中的最大值，MIN()要求指定列名

• SELECT MIN(prod_price)AS min_price FROM products

以上5种聚合函数都支持DISTINCT(排除重复的值)，DISTINCT必须使用列名，不能用于计算或者表达式

组合聚焦函数

SELECT COUNT(*) AS count_all,
MAX(prod_price) AS max_price,
MIN(prod_price) AS　ｍｉｎ＿ｐｒｉｃｅ,
AVG(prod_price) FROM products;

取别名

  在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使我们的SQL更易于理解和使用(以及将来容易排除故障)