9.1 聚集函数

聚集函数（aggregate function），对某些行运行的函数，计算并返回一个值。

使用聚集函数可以汇总数据而不必将涉及的数据实际检索出来。

可利用标准的算术操作符，实现更高级的汇总。

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

9.1.1 AVG()函数

AVG()函数可用来返回特定列或行的平均值。

也可以返回多个列的平均值，此时必须使用多个AVG()函数。

AVG()函数忽略列值为NULL的行。

MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products;
+-----------+
| avg_price |
+-----------+
|  6.823333 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  3.865000 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.1.2 COUNT()函数

可利用COUNT()函数获取表中行的数目或者符合特定条件的行的数目。

COUNT(*)统计表中行的数目，包括NULL的行。
COUNT(column)统计特定列中的行的数目，忽略NULL的行。

MariaDB [sqlbzbh]> SELECT * FROM Customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(cust_email) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.1.3 MAX()函数

MAX()函数一般用来找出最大的数值或日期值。必须指定列名。

在用于文本数据时，则是返回该列排序后的最后一行。

MAX()函数忽略列值为NULL的行。

MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_price) AS max_price FROM Products;
+-----------+
| max_price |
+-----------+
|     11.99 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_name) AS max_prod_name FROM Products;
+---------------+
| max_prod_name |
+---------------+
| Raggedy Ann   |
+---------------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.1.4 MIN()函数

MIN()函数返回最小的数值或日期值。必须指定列名。

在用于文本数据时，则是返回该列排序后的最前面的行。

MIN()函数忽略列值为NULL的行。

MariaDB [sqlbzbh]> SELECT MIN(prod_price) AS min_price FROM Products;
+-----------+
| min_price |
+-----------+
|      3.49 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MIN(prod_name) AS min_prod_name FROM Products;
+--------------------+
| min_prod_name      |
+--------------------+
| 12 inch teddy bear |
+--------------------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.1.5 SUM()函数

SUM()函数用来返回指定列值的和。

SUM()函数忽略列值为NULL的行。

MariaDB [sqlbzbh]> SELECT * FROM OrderItems WHERE order_num = 20005;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | BR01    |      100 |       5.49 |
|     20005 |          2 | BR03    |      100 |      10.99 |
+-----------+------------+---------+----------+------------+
2 rows in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|           200 |
+---------------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|     1648.00 |
+-------------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.2 聚集不同值

对以上5个聚集函数都可以如下使用：

对所有行执行计算，指定ALL参数或不指定参数（ALL参数是默认行为）
只包含不同的值，指定DISTINCT参数。

MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  3.865000 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  4.240000 |
+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

9.3 组合聚集函数

SELECT语句可根据需要包含多个聚集函数。

注意：指定别名，不应该使用表中实际的列名。

MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS avg_price FROM Products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | avg_price |
+-----------+-----------+-----------+-----------+
|         9 |      3.49 |     11.99 |  6.823333 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)MariaDB [sqlbzbh]>

读书笔记–SQL必知必会09–汇总数据

9.1 聚集函数

9.1.1 AVG()函数

9.1.2 COUNT()函数

9.1.3 MAX()函数

9.1.4 MIN()函数

9.1.5 SUM()函数

9.2 聚集不同值

9.3 组合聚集函数

个人收藏笔记记录

9.1 聚集函数

9.1.1 AVG()函数

9.1.2 COUNT()函数

9.1.3 MAX()函数

9.1.4 MIN()函数

9.1.5 SUM()函数

9.2 聚集不同值

9.3 组合聚集函数

个人收藏笔记记录

开通VIP