mysql - mysql中的SEC_TO_TIME函数仅计算group by中的一个条件

Question

我有一个持续时间列，其中包含以秒为单位的值。现在根据我的需要，我必须将其显示为 SEC_TO_TIME。

SELECT wwid,
round(SUM(CASE toc WHEN 'LOCAL' THEN callcost ELSE 0 END),2) AS LOCALPRICE, 
round(SUM(CASE toc WHEN 'STD' THEN callcost ELSE 0 END),2) AS STDPRICE, 
round(SUM(CASE toc WHEN 'ISD' THEN callcost ELSE 0 END),2) AS ISDPRICE,
round(SUM(CASE toc WHEN 'INCOMING' THEN callcost ELSE 0 END),2) AS INCOMINGPRICE, 
count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, 
count(case when toc = 'STD' then 1 end) as STDCALLS, 
count(case when toc = 'ISD' then 1 end) as ISDCALLS, 
count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,
SEC_TO_TIME(CASE toc WHEN 'LOCAL' THEN sum(duration) ELSE 0 END)AS LOCALDUR, 
SEC_TO_TIME(CASE toc WHEN 'STD' THEN sum(duration) ELSE 0 END)AS STDDUR, 
SEC_TO_TIME(CASE toc WHEN 'ISD' THEN sum(duration) ELSE 0 END)AS ISDDUR, 
SEC_TO_TIME(CASE toc WHEN 'INCOMING' THEN sum(duration) ELSE 0 END)AS INCOMDUR,
count(destination) as TOTALCALLS,
SEC_TO_TIME(sum(duration))as TOTALDUR,
round(sum(callcost),2)as TOTALPRICE 
FROM processeddata_table 
WHERE calldate between '2010-10-01' and '2013-10-01'  
group by wwid  
LIMIT 0 , 50

在这里，对于每个 WWID，我们需要以 SEC_TO_TIME 的形式获取持续时间，但对于每个 wwid 持续时间，只有一种调用类型，让它可以是 LOCAL、STD、ISD 等，而其他调用类型也有

任何帮助将不胜感激..

提前致谢..

Answer 1

而不是这个...

SEC_TO_TIME(CASE toc WHEN 'STD' THEN sum(duration) ELSE 0 END)AS STDDUR

...您可能想要的是这个...

SEC_TO_TIME(SUM(CASE toc WHEN 'STD' THEN duration ELSE 0 END)) AS STDDUR

无论哪种 CASE 与组中的每一行匹配，都需要应用 SUM () 聚合函数。这需要在具有类似表达式的所有列上进行更改。