MySQL UNION help

[Big_Pat]

I'm still working on my helpdesk report and am a long way down the line thanks to the help I've received in here.

 

Now, imagine a chart with the list of helpdesk members going across, left to right. Underneath each of them they have two headings (calls logged, calls resolved).

Going down the page we have months of the year. Using the following code I can populate an individual month's calls logged and calls resolved, but is there any way I can do a super-complicated UNION to fill in all the data, or even just one person's which could then be looped? Or is it a case of looping the query, replacing the user and the date?

Here's the code - clearly 'user1' and '2012-11%' need replacing with variables.

SELECT member, SUM(logged) as logged_count, SUM(resolved) as 

resolved_count
FROM
(
    SELECT logged_by as member, COUNT(item) as logged, 0 as 

resolved
    FROM incidents
    WHERE logged_by = 'user1'
    AND logged LIKE '2012-11%'
    GROUP BY logged_by
    UNION
    SELECT resolved_by as member, 0 as logged, COUNT(item) as 

resolved
    FROM incidents
    WHERE resolved_by = 'user1'
    AND logged LIKE '2012-11%'
    GROUP BY resolved_by
) as results

GROUP BY member

[Psycho]

Never run queries in loops. If you intended this report to include more information than calls logged and calls received, you should have mentioned that in your last post.

 

Although you want data to be broken out by month, you don't say if it should be all historical data or just historical data for a specific time period (e.g. last year). The solution will be different. I will assume you need the latter. Even if you don't need it now, you may in the future. So, you will need to have the sub-queries pull ALL the data with no GROUP BY. And then use multiple GROUP BYs on the outer query. I'll see if I can get something together.

 

EDIT: Can you please provide the structure of the two tables. I see you replaced "incident_number" in the query I provided with "item" and not sure why you would have done that. What is the "item" field?

Edited May 13, 2013 by Psycho

[Barand]

try

SELECT member, logged, SUM(log) as logged_count, SUM(resolve) as 
resolved_count
FROM
(
    SELECT logged_by as member, 
    EXTRACT(YEAR-MONTH FROM logged) as logged, 1 as log, 0 as resolve
    FROM incidents
    UNION
    SELECT resolved_by as member, 
    EXTRACT(YEAR-MONTH FROM logged) as logged, 0 as log, 1 as resolve
    FROM incidents
) as results
GROUP BY logged, member

Edited May 13, 2013 by Barand

[Big_Pat]

Certainly, and thanks for the help. (I didn't mention it in my earlier post because I didn't realise that that was the direction I was going!)

My db has three tables: one is departments, one is sd (this contains the servicedesk users, as well as other department employees) and the third is called incidents.

Incidents has: 

• id int 8
• item varchar 100 (the application a user reports an error with)
• ref_no int 10 (the individual reference number given to user)
• user_name varchar 50
• resolving_svd (the dept that closes the incident)
• logged_by varchar 50
• resolved_by varchar 50
• logged date (in the format 2013-05-13)
• date_time varchar - this is actually a string simply because the reporting tool kicks out the date format in UK style. An example entry is 03/12/2012 08:04, which logged shows as 2012-03-12.

• (there are more, but not relating to this query)

 

Barand, thanks for the help but your query failed on EXTRACT (YEAR-MONTH), presumably because of the format of my 'logged' field.

Edited May 13, 2013 by Big_Pat

[Psycho]

What Barand provided was where I was going (but I wasn't aware of the EXTRACT function). But, he has a typo. After looking up the EXTRACT function, the first parameter should be "MONTH_YEAR" not "MONTH-YEAR". Also, I believe in this change the sub query will need to use UNION ALL - otherwise it will only report 1 logged or 1 resolved record in each month for each user

 

That should get you all the results for each user by each month. But, you have another problem to resolve when outputting the data. The query will only return data for each user by month where there is data. So, if a user did not have any logged/resolved issues in a given month, there will not be a record in the result. So, you will need a way in the processing logic to add a zero in the appropriate fields if there was no history for the user in that month. Also, I would make an update to the query to provide a given time period.

SELECT member, logged, SUM(log) as logged_count, SUM(resolve) as resolved_count

FROM
(
    SELECT logged_by as member, 1 as log, 0 as resolve
    EXTRACT(YEAR_MONTH FROM logged) as logged_montYear    
    FROM incidents
    WHERE logged BETWEEN '2013-01-01' AND '2013-02-29'
    UNION ALL
    SELECT resolved_by as member,
    EXTRACT(YEAR_MONTH FROM logged) as logged, 0 as log, 1 as resolve
    FROM incidents
    WHERE logged BETWEEN '2013-01-01' AND '2013-02-29'
) as results

GROUP BY logged, member

Edited May 13, 2013 by Psycho

[Big_Pat]

This didn't quite work, syntax error. I tried adding a comma after the first '0 as resolve', but it still failed. I then tried changing the second 'as logged' to 'as logged_montYear' but I then got 'unknown column "logged" in "field list"'.

 

(I already have a solution in place should any query return '0' or null)

Edited May 13, 2013 by Big_Pat

[Barand]

It now looks as though the EXTRACT should use "logged date" and not "logged" as your query did.

 

 

 

AND logged LIKE '2012-11%'

 

Use DATE or DATETIME or TIMESTAMP column types for dates and times, not varchar, and yyyy-mm-dd format - store dates for functionality not appearance

Edited May 13, 2013 by Barand

[Big_Pat]

Sorry, Barand, I bolded the wrong thing. The column is called logged, and the type is date.

[Barand]

Well I am living in the Home for the Bewildered these days

[Big_Pat]

Here's a layout of what I'm hoping happens:

          User1              User 2         User 3    ----------etc--------TOTALS
        Log | Res | %    Log | Res | %  Log | Res | % --------etc-------- Log | Res | %
Jan
Feb
Mar
Apr


etc

Dec

Totals

Is that any help?

Edited May 13, 2013 by Big_Pat

[Barand]

Store the totals for the users in an array whose key is user.

 

When the month changes, output the array and create a new empty array with user keys.

[Barand]

Each part of a UNION query needs to select the columns in the same order, so the query should be

SELECT member, logged_monthYear, SUM(log) as logged_count, SUM(resolve) as resolved_count
FROM
(
    SELECT logged_by as member, 
        EXTRACT(YEAR_MONTH FROM logged) as logged_monthYear,
        1 as log, 0 as resolve    
    FROM incidents
    WHERE logged BETWEEN '2013-01-01' AND '2013-12-31'
    UNION ALL
    SELECT resolved_by as member,
        EXTRACT(YEAR_MONTH FROM logged) as logged_monthYear, 
        0 as log, 1 as resolve
    FROM incidents
    WHERE logged BETWEEN '2013-01-01' AND '2013-12-31'
) as results

GROUP BY logged, member

[Big_Pat]

Alas, no. 'Unknown column "logged" in group statement'.

 

For testing purposes I tried changing logged BETWEEN '2013-01-01' etc, to LIKE '2013-01%' on both nested queries, but got the same error. 

 

If I could just get the two columns by month for one user, I'd happily loop for the others. There aren't many of them...

[Barand]

GROUP BY logged_monthYear, member

 

 

and

LIKE '2013-01%' will only get 1 month's records

Edited May 14, 2013 by Barand

[Big_Pat]

Thank you! That is just what I needed - plus, you've made space for someone (me, presumably) to live in Bewilderment Manor.