mysql中sum (if)_mysql 中sum (if())

转自：mysql中sum (if)_mysql 中sum (if())_一世红蓝的博客-CSDN博客

先来一个简单的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
————————————————
 

补充：网上找的，未实测过，count（IF（“条件”，符合条件增加值，不符合条件增加值（NULL） ））用法,count不符合条件要用NULL，如果用0也会计数的，跟sum() 不一样

 SELECT DISTINCT c.uid, count( 1 ) AS zongji, count( if( task_type = 'mobile', true, NULL ) ) AS 
 
mobile, count( if( task_type = 'computer', true, NULL ) ) AS computer
FROM keke_witkey_task_work AS c
WHERE c.op_status >0
AND c.free_price >3
AND c.work_time >= '1460176800'
AND c.work_time <= '1460736000'
GROUP BY c.uid
ORDER BY mobile DESC
LIMIT 30 

统计总数示例： 

select  a.卫生院顺序号,count(a.ID) as 体检总数,
count(case when a.是否高血压='是' then 1 else null end)as 高血压总数,
count(case when a.是否糖尿病='是' then 1 else null end)as 糖尿病总数,
count(case when a.是否脑卒中='是' then 1 else null end)as 脑卒中总数,
count(case when a.是否冠心病='是' then 1 else null end)as 冠心病总数
from  表  a   where YEAR(a.tjrq) = DATEPART(year, GETDATE())  GROUP BY a.机构号



sum(case when 字段>0 then 1 else 0 end) as 字段

*注意：count(case when 字段>0 then 1 else 0 end) as 字段

count函数不管记录内容是0或1，它的作用只是计算记录数，如果你要计算次数，用sum(case when 字段>0 then 1 else 0 end) as 字段, 因为你前面计算出来的是0和1的全部次数

或者你用 count(case when 字段>0 then 1 else null end) as 字段这种写法