mysql嵌套分组查询,嵌套分组MySQL查询

我有一个调用详细信息表,需要根据日期分组编译摘要查询.问题是单个呼叫ID可以有多行,如下所示.处置是1 =放弃,2 =处理. RNA = Ring No Answer.

Call ID | Disposition | Call Start | Queue Name | Agent Name | RNA

010001 2 2014-12-03 13:02 Acme_Brick Joe Schmoe FALSE

010002 1 2014-12-03 13:36 Anvils_R_Us Jane Doe TRUE

010003 2 2014-12-03 14:22 Acme_Brick Dan Post TRUE

010003 2 2014-12-03 14:22 Acme_Brick Joe Schmoe FALSE

SET @reportdate = '2014-12-03';

SELECT `Queue Name`,

SUM(CASE WHEN Disposition = 2 THEN 1 ELSE 0 END) AS handled,

COUNT(DISTINCT `Call ID`) AS calls

FROM call_details

WHERE CAST(`Call Start` AS DATE) = @reportdate

GROUP BY `Queue Name`

ORDER BY `Queue Name` ASC;

因此,处理的SUM(处理时为处理= 2 THEN 1 ELSE 0 END)会导致我出现问题.下面的第一个表是我得到的.第二个是它应该是什么.

Queue Name | Handled | Calls

Acme_Brick 3 2

Anvils_R_Us 0 1

Queue Name | Handled | Calls

Acme_Brick 2 2

Anvils_R_Us 0 1

我知道问题在于Disposition字段中的CASE;我只是不确定是否必须创建一个中间摘要表,或者如果有一个复合分组,我可以完成,这将找出多行详细信息调用.提前感谢帮助;).

解决方法:

使用条件COUNT(DISTINCT删除重复项：

SELECT `Queue Name`,

COUNT(DISTINCT CASE WHEN Disposition = 2 THEN `Call ID` END) AS handled,

COUNT(DISTINCT `Call ID`) AS calls

FROM call_details

WHERE CAST(`Call Start` AS DATE) = @reportdate

GROUP BY `Queue Name`

ORDER BY `Queue Name` ASC;

标签：mysql,sql,grouping

来源： https://codeday.me/bug/20190624/1275727.html