Formatting a MySQL Query result

Question

I've currently got a table as follows,

Column      Type
time        datetime             
ticket      int(20)              
agentid     int(20)              
ExitStatus  varchar(50)      
Queue       varchar(50)

I want to write a query which will break this down by week, providing a column with a count for each ExitStatus. So far I have this,

SELECT ExitStatus,COUNT(ExitStatus) AS ExitStatusCount, DAY(time) AS TimePeriod 
FROM `table` 
GROUP BY TimePeriod, ExitStatus

Output:

ExitStatus ExitStatusCount TimePeriod
NoAgentID                1          4 
Success                  3          4
NoAgentID                1          5
Success                  5          5

I want to change this so it returns results in this format:

week | COUNT(NoAgentID) | COUNT(Success) |

Ideally, I'd like the columns to be dynamic as other ExitStatus values may be possible.

This information will be formatted and presented to end user in a table on a page. Can this be done in SQL or should I reformat it in PHP?

So far, i'm thinking I should use GROUP_CONCAT(ExitStatus) and then use PHP to go through the comma delimited fields. Is this the way to go? — Lokicat, Jun 05 '12 at 02:19
Is something like this what you're looking for? http://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql — King Skippus, Jun 05 '12 at 02:20

score 0 · Answer 1 · answered Jun 05 '12 at 02:24

0

This is one way; you can use SUM() to count the number of items a particular condition is true. At the end you just group by the time as per normal.

SELECT DAY(time) AS TimePeriod, 
  SUM('NoAgentID' = exitStatus) AS NoAgentID, 
  SUM('Success' = exitStatus) AS Success, ...
FROM `table` 
GROUP BY TimePeriod

Output:

4 1 3
5 1 5

The columns here are not dynamic though, which means you have to add conditions as you go along.

answered Jun 05 '12 at 02:24

Ja͢ck

170,779
38
263
309

Hmm...perhaps I can do one query to ask for unique ExitStatus and then build your suggested query dynamically from that. I'd hoped for a single query solution but this could work [Edit]: I see Toote just suggested that below] – Lokicat Jun 05 '12 at 02:38

walrii · Answer 2 · 2012-06-05T02:33:29.693

0

SELECT week(time) AS week,
        SUM(ExitStatus = 'NoAgentID') AS 'COUNT(NoAgentID)',
        SUM(ExitStatus = 'Success') AS 'COUNT(Success)'
FROM `table`
GROUP BY week

I'm making some guesses about how ExitStatus column works. Also, there are many ways of interpretting "week", such as week of year, of month, or quarter, ... You will need to put the appropriate function there.

edited Jun 05 '12 at 02:33

answered Jun 05 '12 at 02:24

walrii

3,472
2
28
47

I'm pretty sure this won't work with `COUNT()`, considering that `SELECT COUNT(0)` yields one. – Ja͢ck Jun 05 '12 at 02:29
@Jack I think your are right. Editing to SUM, following your example :) – walrii Jun 05 '12 at 02:32

Toote · Accepted Answer · 2012-06-05T02:42:30.797

There is no "general" solution to your problem (called cross tabulation) that can be achieved with a single query. There are four possible solutions:

Hardcode all possible ExitStatus'es in your query and keep it updated as you see the need for more and more of them. For example:

SELECT
    Day(Time) AS TimePeriod,
    SUM(IF(ExitStatus = 'NoAgentID', 1, 0)) AS NoAgentID,
    SUM(IF(ExitStatus = 'Success', 1, 0)) AS Success
    -- #TODO: Add others here when/if needed
FROM table
WHERE ...
GROUP BY TimePeriod

Do a first query to get all possible ExitStatus'es and then create your final query from your high-level programming language based on those results.
Use a special module for cross tabulation on your high-level programming language. For Perl, you have the SQLCrossTab module but I couldn't find one for PHP
Add another layer to your application by using OLAP (multi-dimensional views of your data) like Pentaho and then querying that layer instead of your original data

You can read a lot more about these solutions and an overall discussion of the subject

This looks like the ticket. Had thought about doing your first suggestion after reading Jacks suggestion. Will go ahead with that. — Lokicat, Jun 05 '12 at 02:40
yup, my first solution is exactly like all others (I also corrected the bug, thanks for pointing it out). I also added other ways to approach the problem too — Toote, Jun 05 '12 at 02:43
True but the part I needed was the suggestion to do an initial query to get unique ExitStatus and build a new query from that in PHP — Lokicat, Jun 05 '12 at 02:44

Formatting a MySQL Query result

3 Answers3