/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 - Service Desk Category Top 10

02/04/2016 1141 views
Hi Guys,

I'm trying to automate some reporting and I'm looking to get a Top 10 of categories for the month. If I try to use a SELECT TOP 10 command I get an error. Here's my script without the TOP 10 command and this spits out an unsorted count of tickets. How can I get it to just give me the TOP 10 calls?

Thanks

SELECT 
HD_CATEGORY.NAME AS Category,
count(HD_TICKET.TITLE) as Count_Of_Tickets
FROM HD_TICKET, HD_CATEGORY
WHERE
HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
AND
(HD_TICKET.HD_QUEUE_ID = 1)
AND
(( date(HD_TICKET.TIME_CLOSED) >= date_sub(curdate(), interval dayofmonth(curdate())-1 day) and month(HD_TICKET.TIME_CLOSED) = month(curdate())))
group by CATEGORY

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

The "TOP 10" syntax is specific to Oracle and does not work in MySQL.  At the bottom of your query (after the "group by CATEGORY" line), add the following.

First, to order the tickets from highest count to lowest:

order by Count_Of_Tickets desc

Second, to get only the first 10 records returned:

limit 10
Answered 02/05/2016 by: grayematter
5th Degree Black Belt

  • Thank you, that's exactly what was needed.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ