我正在建造一个用于每周报告的表,但我从查询开始以了解数据。
下面我有我的主查询,该查询目前正在为我的主要总数工作,但是我现有的"总体"列需要一个伙伴列,该列将显示不同的总数。基本上,这一切都是与电话有关的,因此我目前有电话总计(总通话,总错过,总入站,总出站),但是我需要一个额外的列来计算每个已知呼叫的量我们数据库中的数字。
我有一个名为ambition.ambition_customer_data
的表,带有一个名为phone_number
的字段。因此,如果您在我的查询中查看大约8行,我需要得到一个总和,如果有意义的话,calledpartyno = ambition_customer_data.phone_number OR finallycalledpartyno = ambition_customer_data.phone_number
的数字。
对于其他列,我需要做同样的事情,但是我必须将其基于下面的legtype
列,但是如果我对如何进行此操作有一个想法,我可以将其应用于这些。我希望我可以做一个求和(in in in in in in in in in(select phone_number来自bambition_customer_data)),但这似乎不起作用。
我当前有一个不完整的语句。关于我可以做到这一点的最佳方法的任何提示?
SELECT
u.firstn
, u.lastn
, c.extension
, SUM(IF(LEGTYPE1 = 1, 1, 0)) + -- outbound calls
SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) + -- inbound calls
SUM(IF(Answered = 1, 0, 1)) AS Total_Calls
, sum(if calledpartyno =) /*HERE IS MY ISSUE CURRENTLY*/
, sum(if(Answered = 1,0,1)) AS Total_Missed
, sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) AS Total_Recieved
, sum(if(LEGTYPE1 = 1,1,0)) AS Total_Outbound
, round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
, sum(if(Answered = 1,0,1)) / (SUM(IF(LEGTYPE1 = 1, 1, 0)) + -- outbound calls
SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) + -- inbound calls
SUM(IF(Answered = 1, 0, 1))) * 100 AS Percentage_Missed
FROM ambition.session a
INNER JOIN ambition.callsummary b
ON a.NOTABLECALLID = b.NOTABLECALLID
INNER join ambition.mxuser c
ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
INNER join jackson_id.users u
on c.extension = u.extension
WHERE b.ts between curdate() - interval 5 day and now()
AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
group by c.extension;
您正在寻找这样的东西。
sum( prentig_here时然后 column_to_select 结束)为`selection_name`