Hi all, I'm trying to add an additional field to the following SQL query to add custom5 in the results.  This is a pull down for the number of devices that the request is for.  Thanks in advance!

 

SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets' FROM HD_TICKET T

JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)

LEFT JOIN USER O ON (T.OWNER_ID = O.ID)

WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 365)

GROUP BY O.FULL_NAME

order by COUNT(T.ID) DESC

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

Custom Value 5 is stored in the database as CUSTOM_FIELD_VALUE4 so you could change the SELECT portion of the query to be:

 SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', CUSTOM_FIELD_VALUE4 as '# of Devices'

I'm not sure if this will produce the results that you're looking for, however. Your current query will produce a list of owners and how many tickets they have, but adding a specific field from individual tickets will change that result. What exactly are you trying to report on? Do you want the sum of values reported in the custom field?

Answered 10/22/2012 by: chucksteel
Red Belt

  • Thank you for getting back to me.

    Yes, I want to be able to sum up the value choosen in the # of devices field. This will allow me to run a quick report that will show me the number of tickets a technician complete, as well as the number of devices they touched. Many times people in computer labs and libraries will put in a single ticket for 30 or so machines... If I just report on the number of tickets the guys complete it really won't reflect how much work they really did...
  • In that case using SUM(CUSTOM_FIELD_VALUE4) should work.
  • Thanks again! I added this line and the report runs, but the sum is 0 for everyone now... Could it be that since the CUSTOM_5 field is a sigle select field it's not adding them up? Here is what I have right now:

    SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', SUM('CUSTOM_5') AS '# of Devices' FROM HD_TICKET T
    JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
    LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
    WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 5)
    GROUP BY O.FULL_NAME
  • I downloaded the MySQL work bench and believe I had the table name wrong, I set it to the following but still have no results.

    SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', SUM('CUSTOM_FIELD_VALUES5') AS '# of Devices' FROM HD_TICKET T
    JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
    LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
    WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 5)
    GROUP BY O.FULL_NAME
  • If your field is custom value 5 then the column in the table is CUSTOM_FIELD_VALUE4. The columns in the database are 0 based so you always have to subtract one from the number in the configuration for the table's column.
Please log in to comment
Answer this question or Comment on this question for clarity

Share