Home > Database > Mysql Tutorial > body text

What is the method of using mysql sum?

藏色散人
Release: 2023-02-14 09:42:57
Original
2748 people have browsed it

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".

What is the method of using mysql sum?

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
Copy after login

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
Copy after login

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  '别名'
Copy after login

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();
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact [email protected]
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!