Best Practices

Are you looking for a count of tickets with any values in either custom1 or custom2 (a simple count)? Such as:

Tickets with any value in Custom1 or Custom2: 45

Are you looking for counts grouped by the values of custom1 and custom2 (a data cube)? Such as:

Custom1 Custom2 Count
value_a value_b 3
value_a Value_c 5
value_d value_b 10
value_d value_e 14 - grayematter 10 years ago

I am looking for a count of custom field value categories on all tickets for all our queues. On CUSTOM_FIELD_VALUE1 is our "Ticket Type" category with values "Break Fix" or "Service Request" while our CUSTOM_FIELD_VALUE2 is our "Ticket Source" category with values/choices "Phone" "Web Browser" "Email" or "Chat"

Thanks, - k100user 10 years ago

I think this is what you are trying to get at.

SELECT HD_STATUS.NAME AS STATUS,
HD_TICKET.CUSTOM_FIELD_VALUE2,
HD_TICKET.CUSTOM_FIELD_VALUE1,
count(*) as count_of_both
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID in (5,2,8,9,12,14,18,16,1,15,10,11,3,13,17,25,20))
AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 30 DAY)))
GROUP BY HD_STATUS.NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1
ORDER BY HD_STATUS.NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1

Something like this will give you data that you can import into an excel pivot table. You can then slice and dice the data as you wish. If you want to add the option to view counts by queue, add HD_TICKET.HD_QUEUE_ID to the select, group by, and order by clauses. - grayematter 10 years ago

The sql query above did not work/show the needed result. It did not present a total value of the custom field values for each queue. Each Queue should be listed as a row with columns for total amounts of values in custom field value 1 and custom field value 2.

Thanks - k100user 10 years ago