sum
功能
返回指定列所有值的总和。该函数会忽略 NULL 值,可以与 DISTINCT 运算符搭配使用。
语法
SUM(expr)
参数说明
expr
: 用于指定参与求和运算的列。列值可以为 TINYINT,SMALLINT,INT,FLOAT,DOUBLE,或DECIMAL 类型。
返回值说明
列值和返回值类型的映射关系如下:
- TINYINT -> BIGINT
- SMALLINT -> BIGINT
- INT -> BIGINT
- FLOAT -> DOUBLE
- DOUBLE -> DOUBLE
- DECIMAL -> DECIMAL
如果没有找到匹配的列,则返回报错。 如果某行的值为NULL,该行不参与计算。 如果列值为 STRING 类型的数字,会隐式转换为 DOUBLE 类型后再进行运算。
示例
-
创建表
employees
。CREATE TABLE IF NOT EXISTS employees (
region_num TINYINT COMMENT "range [-128, 127]",
id BIGINT COMMENT "range [-2^63 + 1 ~ 2^63 - 1]",
hobby STRING NOT NULL COMMENT "upper limit value 65533 bytes",
income DOUBLE COMMENT "8 bytes",
sales DECIMAL(12,4) COMMENT ""
)
DISTRIBUTED BY HASH(region_num) BUCKETS 8; -
向表中插入数据。
INSERT INTO employees VALUES
(3,432175,'3',25600,1250.23),
(4,567832,'3',37932,2564.33),
(3,777326,'2',null,1932.99),
(5,342611,'6',43727,45235.1),
(2,403882,'4',36789,52872.4); -
查询表数据。
MySQL > select * from employees;
+------------+--------+-------+--------+------------+
| region_num | id | hobby | income | sales |
+------------+--------+-------+--------+------------+
| 5 | 342611 | 6 | 43727 | 45235.1000 |
| 2 | 403882 | 4 | 36789 | 52872.4000 |
| 4 | 567832 | 3 | 37932 | 2564.3300 |
| 3 | 432175 | 3 | 25600 | 1250.2300 |
| 3 | 777326 | 2 | NULL | 1932.9900 |
+------------+--------+-------+--------+------------+
5 rows in set (0.01 sec) -
使用
sum
函数进行求和运算。示例1:计算各地区的销售额总和,即以
region_num
进行分组对sales
求和。MySQL > SELECT region_num, sum(sales) from employees
group by region_num;
+------------+------------+
| region_num | sum(sales) |
+------------+------------+
| 2 | 52872.4000 |
| 5 | 45235.1000 |
| 4 | 2564.3300 |
| 3 | 3183.2200 |
+------------+------------+
4 rows in set (0.01 sec)示例2:计算各地区员工的收入总合,即以
region_num
进行分组对income
进行求和。因为sum
函数忽略 NULL 值,因此id
为777326
的员工收入没有参与计算。MySQL > select region_num, sum(income) from employees
group by region_num;
+------------+-------------+
| region_num | sum(income) |
+------------+-------------+
| 2 | 36789 |
| 5 | 43727 |
| 4 | 37932 |
| 3 | 25600 |
+------------+-------------+
4 rows in set (0.01 sec)示例3:计算员工爱好数总和。其中
hobby
列为STRING类型的数字,在计算时会进行隐式转换为DOUBLE类型。MySQL > select sum(DISTINCT hobby) from employees;
+---------------------+
| sum(DISTINCT hobby) |
+---------------------+
| 15 |
+---------------------+
1 row in set (0.01 sec)示例4:结合 WHERE 条件子句,计算月收入大于30000的员工收入总和。
MySQL > select sum(income) from employees
WHERE income > 30000;
+-------------+
| sum(income) |
+-------------+
| 118448 |
+-------------+
1 row in set (0.00 sec)