What is the method of using mysql sum?

Mysql sum sum method: 1. Implement a single sum through "select sum(value) as value from table where user_id"; 2. Sum up multiple conditions through nested statements, with syntax such as "( select sum(value) from table where type = 6 and type_son = 1) as xj0".

The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.

What is the method to use mysql sum?

MySQL SUM() Conditional summation method and multi-condition summation method

1. Single summation.

select sum(value) as value from table where user_id = 1 and type = 6 and type_son = 2

value is the field to be summed.

as is followed by sum and gives it a name after summing.

2. Multi-condition summation of nested statements in SQL statements.

select 
 
(select sum(value) from table where type = 6 and type_son = 1) as xj0,
 
(select sum(value) from table where type = 6 and type_son = 2) as xj1,
 
(select sum(value) from table where type = 3 and type_son = 3) as xj2,
 
(select sum(value) from table where type = 4 and type_son = 3) as xj3
 
from table where user_id = 1 limit 0,1

as is followed by sum and gives it a name after summing so there will be no conflict.

3. Same as the second one, but instead of summing by nesting statements, sum is used to judge the sum.

select 
 
sum(IF(type = 6 and type_son = 1,value,NULL)) as xj0,
 
sum(IF(type = 6 and type_son = 2,value,NULL)) as xj1,
 
sum(IF(type = 3 and type_son = 0,value,NULL)) as xj2,
 
sum(IF(type = 4 and type_son = 3,value,NULL)) as xj3
 
from table where user_id = 1
 
 
sum(IF('条件判断'，'求和的字段'，'NULL不计算'))  as  '别名'

I think the third way is better than the first two ways.

YII 2.0 uses SUM to calculate the sum

$v['alls_bonus'] = AccountingLog::find()
        ->select(["
            sum( IF(type = 6 and type_son = 1,value,NULL) ) as xj0,
            sum( IF(type = 6 and type_son = 4,value,NULL) ) as xj1,
            sum( IF(type = 8 and type_son = 4,value,NULL) ) as xj2, 
            sum( IF(type = 3 and type_son = 1,value,NULL) ) as xj3
        "])
        ->where(['user_id'=>1])
        ->asArray()
        ->one();

Be careful to add ["sum..."] in the select, otherwise an error will be reported

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of What is the method of using mysql sum?. For more information, please follow other related articles on the PHP Chinese website!