MySQL mysql中的总和（IF）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17130161/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum(IF) in mysql
提问by Monisha
Can I get the sum of bug count with multiple conditions in mysql?
我可以在 mysql 中获得多个条件下的错误计数总和吗？
My requirement is, I need to get the sum or the count of bugids having some priority, say P1, which exceed a particular time, but the exceeded count should be calculated from the total count of that particular Priority.
我的要求是，我需要获得具有某些优先级的 bugid 的总和或计数，比如 P1，超过特定时间，但超出的计数应根据该特定优先级的总数计算。
My query is:-
我的查询是：-
select
sum(
IF(priority="P1",1,0)) P1,
sum(
IF(timediff(delta_ts,creation_ts) > "00:00:05",1,0))P1_exeeded,
SUM(
IF(priority="P2",1,0)) P2,
sum(
IF(timediff(delta_ts,creation_ts) > "00:00:10",1,0))P2_exeeded,
SUM(
IF(priority="P3",1,0)) P3count,
SUM(
IF(priority="P4",1,0)) P4count,
SUM(
IF(priority="P5",1,0)) P5count,
SUM(
IF(priority="P6",1,0)) P6count,
SUM(
IF(priority="P7",1,0)) P7count,
SUM(
IF(priority="P8",1,0)) P8count
from bugs
where bugs.product_id=237
and bugs.resolution='FIXED'
and bugs.creation_ts >='2013-06-14 09:00:00'
and bugs.creation_ts <= '2013-06-16 08:59:59'
and bug_status="RESOLVED";
I got the result as:-
我得到的结果是：-
------+------------+------+------------+---------+---------+---------+---------+---------+---------+ | P1 | P1_exeeded | P2 | P2_exeeded | P3count | P4count | P5count | P6count | P7count | P8count | +------+------------+------+------------+---------+---------+---------+---------+---------+---------+ | 7 | 19 | 6 | 18 | 5 | 1 | 0 | 0 | 0 | 0 | +------+------------+------+------------+---------+---------+---------+---------+---------+---------+ 1 row in set (0.00 sec)
But I need the sum of the exceeded bug ids from each priority, by taking two conditions in :-
但是我需要通过以下两个条件来计算每个优先级中超出的错误 ID 的总和：-
sum of exceeded bugids above a particular time limit, and its taken from the count of its particular priority category.
超过特定时间限制的超出 bugid 的总和，并取自其特定优先级类别的计数。
the exceeded sum of bug ids should satisfy both condition in each case.
在每种情况下，超出的错误 ID 总和都应满足这两个条件。
P1 priority bug ids total I have. P1_exeeded = IF(timediff(delta_ts,creation_ts) > "00:00:05",1,0)) taken from the the count of P1 bugids.
我拥有的 P1 优先级错误 ID 总数。P1_exeeded = IF(timediff(delta_ts,creation_ts) > "00:00:05",1,0)) 取自 P1 bugid 的计数。
Please help to use sum( satisfying 2 conditions) in mysql
请帮助在mysql中使用sum（满足2个条件）
回答by Monisha
I got the answer. Thanks Chetan.:-
我得到了答案。谢谢车坦。:-
select
sum(
IF(priority="P1",1,0)) P1,
sum(
IF((timediff(delta_ts,creation_ts) > "00:02:00") && (priority="P1") ,1,0))P1_exeeded,
SUM(
IF(priority="P2",1,0)) P2,sum( IF((timediff(delta_ts,creation_ts) > "00:01:00") && (priority="P2") ,1,0))P2_exeeded,
SUM(
IF(priority="P3",1,0)) P3count,
SUM(
IF(priority="P4",1,0)) P4count,
SUM(
IF(priority="P5",1,0)) P5count,
SUM(
IF(priority="P6",1,0)) P6count,
SUM(
IF(priority="P7",1,0)) P7count,
SUM(
IF(priority="P8",1,0)) P8count
from bugs ....
where
.....
+------+------------+------+------------+---------+---------+---------+---------+---------+---------+ | P1 | P1_exeeded | P2 | P2_exeeded | P3count | P4count | P5count | P6count | P7count | P8count | +------+------------+------+------------+---------+---------+---------+---------+---------+---------+ | 7 | 1 | 6 | 1 | 5 | 1 | 0 | 0 | 0 | 0 | +------+------------+------+------------+---------+---------+---------+---------+---------+---------+
回答by KeepLearning
SELECT `customer xxx`,
cxType,
`Order Store`,
sum(if(category='xxxx',qty,0)),
min(`order date`) as 'First Purchcase Date'
FROM table1
group by `customer xxx`;
