Home  >  Article  >  Database  >  What is the method of using mysql sum?

What is the method of using mysql sum?

藏色散人
藏色散人Original
2023-02-14 09:42:572894browse

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

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!

Statement:
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 admin@php.cn