一、简单描述
sum (column ) : 把一列的值全部相加
sum( if ( 条件 , 2 , 0 ) ) : 满足“条件”,加2,不满足条件,加0
二、小栗子
1.例:表user如图
统计每个用户count<5的次数
select user_id,sum(IF(count<5,1,0)) as times from user group by user_id;
结果如下:
2.
select sum(qty) as total_qty from inventory_product group by product_id
这样就会统计出所有product的qty.
但是很不幸,我们的系统里面居然有qty为负值。而我只想统计那些正值的qty,加上if function就可以了。 SQL为:
select sum(if(qty > 0, qty, 0)) as total_qty from inventory_product group by product_id
意思是如果qty > 0, 将qty的值累加到total_qty, 否则将0累加到total_qty.
以下是sum(if())的例子:
select
sum( if( qty > 0, qty, 0)) as total_qty ,
sum( if( qty < 0, 1, 0 )) as negative_qty_count
from inventory_product
group by product_id