CODEWITHSUNDEEP
mysqlsql
Geoff_S
Geoff_S

Reputation: 5107

aggregating by grouping, not just one record of totals

I have a query below that should be totaling up numbers by CSR agent using their extension. Currently it's returning one line/record for one agent and her extension but it's showing the correct totals for all 15 agents on that one record.

However, what I want is for it to list each agent and their extension with their own respective totals for the day. I'm grouping by extension but it doesn't seem to fix the issue.

Is there something in my query that would stick out as a reason for it not breaking my totals up per extension/agent?

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;

Upvotes: 0

Views: 48

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Your GROUP BY clause is required inside the subquery. Also I suspect you can avoid the right join by using FROM jackson_id.users c. Also you are usingca left join to ambition.known_numbers k but many of the case expressions rely on a non-null value from that table. Either the join should be an inner join or you need to cater for NULLs from that table in those case expressions.

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
;

Personally I have never been keen on table aliases that rely on sequence (a,b,c ...) as that sequence may not remain steady. Instead, I much prefer first-letter-of-each-word where this is possible. users = u, callsummary = cs, sessions = s, known_numbers = kn etc.

Upvotes: 2

Related Questions