转自: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 字段这种写法