mysql sum if

转:http://blog.csdn.net/k8080880/article/details/17392341

mysql> select sum(if(classid=1,1,0)) as blue,sum(if(classid=2,1,0)) as red from article;

+------+------+
| blue | red  |
+------+------+
|  221 |  274 |
+------+------+
1 row in set (0.00 sec)

mysql> select count(classid=1 or null) as blue,count(classid=2 or null) as red from article;
+------+-----+
| blue | red |
+------+-----+
|  221 | 274 |
+------+-----+

1 row in set (0.01 sec)

先来一个简单的sum

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.

再加强一点:

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

    A+
发布日期:2016年10月18日  所属分类:未分类

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: