mysql查询: count column

Question

大家好，我有一个技术问题，我有一张记录如下的表格:

DATE        SIP DURATION  TYPE
2017-02-22  670    9      NO ANSWER
2017-02-22  670    9      ANSWER
2017-02-22  670    9      ANSWER
2017-02-22  671    9      NO ANSWER

我想要这样的结果:

SIP   DURATION  NOANSWER  ANSWER
670     18         1        2
671     9          1        0

我会这样查询:

select 
DISTINCT(SIP),
(select count(*) FROM table where TYPE="ANSWERED")
(select count(*) FROM table where TYPE="NO ANSWERED")
from table

但是结果统计了表中的所有记录，是不是用group by比较好？

对不起我是菜鸟:(谢谢你的时间马可

Answer 1

在这种情况下，将 GROUP BY 与条件 SUM 和 COUNT 一起使用会更容易，如下例所示:

SELECT 
    SIP,
    SUM(CASE TYPE WHEN 'ANSWER' THEN DURATION ELSE 0 END) AS DURATION,
    COUNT(CASE WHEN TYPE = 'NO ANSWER' THEN 1 ELSE NULL END) AS NOANSWER,
    COUNT(CASE WHEN TYPE = 'ANSWER' THEN 1 ELSE NULL END) AS ANSWER
FROM table_name
GROUP BY SIP

Demo: http://sqlfiddle.com/#!9/b1142/2/0