mysql - 通过分组进行汇总，而不仅仅是一条总计记录

Question

我在下面有一个查询，应该由 CSR 代理使用其扩展名来汇总数字。目前，它返回一名客服人员及其分机的一行/记录，但显示了该记录上所有 15 名客服人员的正确总计。

但是，我想要的是它列出每个代理及其分机以及当天各自的总计。我按扩展名进行分组，但似乎无法解决问题。

我的查询中是否存在某些突出的原因，导致它没有分解每个分机/代理的总数？

SELECT  firstn
, lastn
, extension
, Recieved
, RecievedKnown
, Outbound
, outboundKnown
, Missed
, MissedKnown
, duration
, HOLDTIMESECS


    FROM (
  SELECT firstn
   , lastn
   , c.extension
   , sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Recieved
   , sum(case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as RecievedKnown
   , sum(CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 then 1 ELSE 0 end)  AS Outbound
   , sum(case when LEGTYPE1 = 1 and FINALLYCALLEDPARTYNO = k.phone_number then 1 ELSE 0 end) as outboundKnown
   , sum(case when Answered = 0 then 1 ELSE 0 end) as Missed
   , sum(case when ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end)   as MissedKnown
   , sum(b.duration) as duration
   , sum(a.holdtimesecs) as holdtimesecs


  FROM ambition.session a
  INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
  right join jackson_id.users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
  LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
  WHERE a.ts >= curdate()

  and(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) OR a.finallycalledpartyno IN (select extension from ambition.ambition_users))
  ) x
  group by extension;

Answer 1

子查询内需要您的 GROUP BY 子句。另外我怀疑您可以使用 FROM jackson_id.users c 来避免正确的连接。此外，您还使用左连接到 ambition.known_numbers k 但许多 case 表达式依赖于该表中的非空值。连接应该是内部连接，或者您需要在这些 case 表达式中满足该表中的 NULL。

SELECT
        firstn
      , lastn
      , extension
      , Recieved
      , RecievedKnown
      , Outbound
      , outboundKnown
      , Missed
      , MissedKnown
      , duration
      , HOLDTIMESECS
FROM (
        SELECT
              firstn
            , lastn
            , c.extension
            , sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Recieved
            , sum(case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as RecievedKnown
            , sum(CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 then 1 ELSE 0 end)  AS Outbound
            , sum(case when LEGTYPE1 = 1 and FINALLYCALLEDPARTYNO = k.phone_number then 1 ELSE 0 end) as outboundKnown
            , sum(case when Answered = 0 then 1 ELSE 0 end) as Missed
            , sum(case when ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end)   as MissedKnown
            , sum(b.duration) as duration
            , sum(a.holdtimesecs) as holdtimesecs
        FROM jackson_id.users c
        INNER JOIN ambition.session a  on c.extension = a.callingpartyno or c.extension = a.finallycalledpartyno
        INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
        LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
        WHERE a.ts >= curdate()
              AND(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) 
               OR a.finallycalledpartyno IN (select extension from ambition.ambition_users)
                 )
        GROUP BY
              firstn
            , lastn
            , c.extension
      ) x
;

就我个人而言，我从来不热衷于依赖序列(a，b，c ...)的表别名，因为该序列可能不会保持稳定。相反，如果可能的话，我更喜欢每个单词的第一个字母。用户 = u、callsummary = cs、 session = s、known_numbers = kn 等。