How do you return max values by day in MySQL?

Question

Environment

Table agent_poll records the total calls a call centre agent makes, polling the telephone system every 1 second to request the updated total. This provides many records per agent per day.

This is described as follows.

DESCRIBE agent_poll
Field        |Type        |Null|Key|Default             |Extra         |
-------------|------------|----|---|--------------------|--------------|
agent_poll_id|int(11)     |NO  |PRI|                    |auto_increment|
poll_time    |timestamp(6)|NO  |   |CURRENT_TIMESTAMP(6)|              |
agent_id     |int(11)     |NO  |MUL|                    |              |
agent_status |int(11)     |NO  |MUL|                    |              |
total_calls  |int(11)     |NO  |   |                    |              |

Here's the top ten rows - junk demo data so not as regular as it would be in production.

select * FROM agent_poll
agent_poll_id|poll_time                    |agent_id|agent_status|total_calls|
-------------|-----------------------------|--------|------------|-----------|
            1|          2019-11-12 12:02:01|       1|           1|         12|
            2|          2019-11-12 12:30:01|       4|           1|         12|
            3|          2019-11-12 12:34:18|       6|           4|         22|
            4|          2019-11-12 12:44:07|       1|           4|         22|
            5|          2019-11-14 12:15:44|       3|           3|          4|
            6|          2019-11-14 12:16:07|       1|           3|         23|
            7|          2019-11-14 12:21:42|       2|           3|          4|
            8|          2019-11-14 12:21:58|       5|           3|          4|
            9|          2019-11-14 12:22:47|       1|           1|         25|
           10|          2019-11-14 12:30:57|       2|           1|          4|

Additionally, here is the agent status table.

select * from agent_status
agent_status_id|agent_status_description|
---------------|------------------------|
              1|Available               |
              2|Break                   |
              3|Admin                   |
              4|On a Call               |
              5|Sick                    |
              6|Holiday                 |
              7|Away                    |

Problem

How do you write this query?

If agent_poll.agent_status is 5, 6 or 7, return agent_status, else
Return the maximum total_calls calls taken by each agent for each week day

Restrictions

Should be dynamic to allow for future table expansion - more agents, agent status codes etc
Should always look up the most recent date

Output should look like this.

Agent|Mon|Tue|Wed|Thu|Fri|
-----|---|---|---|---|---|
    1|   |   |   |   |   |
    2|   |   |   |   |   |
    3|   |   |   |   |   |
    4|   |   |   |   |   |
    5|   |   |   |   |   |
    6|   |   |   |   |   |
    7|   |   |   |   |   |
    8|   |   |   |   |   |
    9|   |   |   |   |   |
   10|   |   |   |   |   |
   11|   |   |   |   |   |
   12|   |   |   |   |   |
   13|   |   |   |   |   |

What I have Tried

I know that it is possible to pivot given the correct permissions. Since I am outputting to Grafana, which can only have read-only permissions, I cannot CREATE, UPDATE, or DROP.

One aproach is to use a virtual table. This was suggested to me by a TSQL dev.

;WITH beepboop AS (
SELECT  B.agent_id,
        CASE WHEN WEEKDAY(B.poll_time) = 'Monday' THEN MAX(B.total_calls) ELSE NULL END AS 'Monday Calls', #one bin for the data to go into
        CASE WHEN WEEKDAY(B.poll_time) = 'Tuesday' THEN MAX(B.total_calls) ELSE NULL END AS 'Tuesday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Wednesday' THEN MAX(B.total_calls) ELSE NULL END AS 'Wednesday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Thursday' THEN MAX(B.total_calls) ELSE NULL END AS 'Thursday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Friday' THEN MAX(B.total_calls) ELSE NULL END AS 'Friday Calls'
FROM agent_poll AS B
WHERE B.poll_time > GETDATE() -7 --the last week
GROUP BY B.agent_id, DATENAME(WEEKDAY, B.poll_time) #Have to group by the datename as well as we're using it as part of the filter so it can't be aggregated
)
SELECT BB.agent_id, 
    MAX(BB.[Monday Calls]) AS 'Monday Calls', #now we are only grouping by the agent so we aggregate the bins 
    MAX(BB.[Tuesday Calls]) AS 'Tuesday Calls', 
    MAX(BB.[Wednesday Calls]) AS 'Wednesday Calls', 
    MAX(BB.[Thursday Calls]) AS 'Thursday Calls',
    MAX(BB.[Friday Calls]) AS 'Friday Calls'
FROM beepboop AS BB
GROUP BY BB.agent_id # only group by agent now

Unfortunately, I have not been successful in getting this to work in MySQL. I am therefore left with the assumption that it needs to be built from many sub-queries, but cannot get them in the right places. This returns the agent_id along with the dates for the current week.

SELECT agent_id as "Agent",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Monday'), '%X%V %W') as "Mon",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Tuesday'), '%X%V %W') as "Tue",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Wednesday'), '%X%V %W') as "Wed",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Thursday'), '%X%V %W') as "Thu",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Friday'), '%X%V %W') as "Fri"
from agent
order by Agent

Agent|Mon       |Tue       |Wed       |Thu       |Fri       |
-----|----------|----------|----------|----------|----------|
    1|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    2|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    3|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    4|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    5|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    6|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    7|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    8|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    9|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
   10|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|

I believe I just need to extend each of the day lines to use the dates they provide as a condition. Where should I go from here?

Your code is for ms sql server, not for mysql! I changed the tags to reflect this, but pls pay attention next time! — Shadow, Nov 18 '19 at 13:26
I am using MySQL, not SQL Server, so would be grateful of some direction on the dialect — James Geddes, Nov 18 '19 at 13:26
The above code will not run in mysql. If grafana has its own set of commands and functions and you use those as opposed to directly interacting with the database, then your underlying database is irrelevant. — Shadow, Nov 18 '19 at 13:29
Grafana runs on the database that you give it, but just without write access — James Geddes, Nov 18 '19 at 13:30
Does it have its own set of functions, like tableau, or do you have to write mysql specific sql queries? — Shadow, Nov 18 '19 at 13:32
Does the last table correspond to the result that you expect? — GMB, Nov 18 '19 at 13:33
Also, you should really specify what you need help with. There are dozens of answered questions here on SO on pivoting in mysql that could give you a start. You even linked one of them, then went with an ms sql solution, which obviously did not work. Have you tried modifying the one that you linked in the question? — Shadow, Nov 18 '19 at 13:35
@Shadow Grafana does not have its own functions, it just graphs the results of the query you give it. — James Geddes, Nov 18 '19 at 13:40
@GMB I am looking for results that look like the last table, but it should show the last total_calls value for that agent and day, rather than returning the date — James Geddes, Nov 18 '19 at 13:41
@Shadow Apologies if I was unclear. I am trying to write a query that returns the maximum of total_calls for each day and agent. — James Geddes, Nov 18 '19 at 13:43
Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Nov 18 '19 at 14:53

GMB · Accepted Answer · 2019-11-18T14:16:41.907

1

You could use a correlated subquery to filter on the last record per agent and weekday, and then do conditional aggregation:

select 
    agent_id agent,
    max(case when weekday(poll_time) = 0 then total_calls end) mon,
    max(case when weekday(poll_time) = 1 then total_calls end) tue,
    max(case when weekday(poll_time) = 2 then total_calls end) wed,
    max(case when weekday(poll_time) = 3 then total_calls end) thu,
    max(case when weekday(poll_time) = 4 then total_calls end) fri
from agent a
where poll_time = (
    select max(polltime)
    from agent a1
    where 
        a.agent_poll_id = a1.agent_poll_id 
        and weekday(a.poll_time) = weekday(a1.poll_time)
)
group by agent_id

edited Nov 18 '19 at 14:16

answered Nov 18 '19 at 13:32

GMB

216,147
25
84
135

Super! Though this returns for all rows in the agent_poll table. We need to see the total by day for each agent_id, rather than the agent_poll_id. Also, how would you include the "If agent_poll.agent_status is 5, 6 or 7, return agent_status"? – James Geddes Nov 18 '19 at 14:15
I replaced `agent_poll_id` with `agent_id`. So if `agent_status` is 5, 6, or 7 you want to display the `agent_status` instead of `total_calls`? – GMB Nov 18 '19 at 14:18
Great! That is correct - if an agent is on holiday the cannot be expected to make calls :) – James Geddes Nov 18 '19 at 14:22
Just to avoid confusion, the query will return the agent_status_description from agent_status if agent_status in (5,6,7) – James Geddes Nov 18 '19 at 14:24
@JamesGeddes: no it is not possible to return the agent description, because it has a different datatype than `total_calls`. I would suggest returning a `null` value if the agent is on holiday, this makes more sense in my opinion.Or maybe return a distinctive number, like `-1`. – GMB Nov 18 '19 at 14:26
Could one return everything as a string? Apparently we need the report to show total calls or the reason they are not making any. – James Geddes Nov 18 '19 at 14:31

How do you return max values by day in MySQL?

1 Answers1