CODEWITHSUNDEEP
mysqlsqlgrouping
jconnors
jconnors

Reputation: 93

Nested groupings MySQL query

I have a table of call details and need to compile a summary query based on date grouping. The trouble is that a single Call ID can have multiple rows, as you see below. Disposition is 1 = Abandoned, 2 = Handled. 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;

So, the SUM(CASE WHEN Disposition = 2 THEN 1 ELSE 0 END) AS handled, is causing me issues. The first table below is what I am getting. The second is what it should be.

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

I know the issue sits with the CASE on the the Disposition field; I'm just not sure if I have to create an intermediary summary table, or if there is a compound grouping I can complete that will ferret out the multi-row detail calls. Appreciate the help in advance ;).

Upvotes: 4

Views: 65

Answers (3)

TommCatt
TommCatt

Reputation: 5636

It occurred to me that if RNA is true, then the disposition should not be counted. I changed the CASE statement to

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

and got the result you wanted. But that is based on the limited data set you provided. It may not hold true but depends on how your business rules on how unanswered calls are considered.

Upvotes: 0

Barmar
Barmar

Reputation: 780974

Use a subquery to get rid of the duplicates:

SELECT `Queue Name`, SUM(Disposition = 2) AS handled, COUNT(*) AS calls
FROM (
    SELECT `Queue Name`, `Call ID`, MAX(Disposition) AS Disposition
    FROM call_details
    WHERE CAST(`Call Start` AS Date) = @reportdate
    GROUP BY `Queue Name`, `Call ID`) AS subq
GROUP BY `Queue Name`
ORDER BY `Queue Name`

Upvotes: 1

user359040
user359040

Reputation:

Use a conditional COUNT(DISTINCT to remove duplicates:

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;

Upvotes: 3

Related Questions