Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TOP 5 Query with Others

Status
Not open for further replies.

jjohns09

Technical User
Jun 26, 2001
15
US
I need help with my query. I am trying to write a query from a table of help desk tickets that returns information on the Top 5 affected product types. I am using several fields from the Tickets table, TICKET_ID, PRODUCT_TYPE and INTIAL_RESOLVE. The INITIAL_RESOLVE field contains either a 'T' or an 'F'. My query below counts the total number of tickets by product type as well as how many tickets were initially resolved and how many had to be dispatched. My problem is that not only do I want the top 5 but I also want the same statistics for all Others as one row. Can anyone help??

Existing query:
SELECT TOP 5
PRODUCT_TYPE,
COUNT(TICKET_ID) - SUM(INITIAL_RESOLVE) AS DISPATCHED,
SUM(INITIAL_RESOLVE) AS RESOLVED,
COUNT(TICKET_ID) AS TOTAL

FROM
(SELECT
TICKET_ID,
PRODUCT_TYPE,

CASE
WHEN INITIAL_RESOLVE = 'T' THEN 1
ELSE 0
END AS INITIAL_RESOLVE

FROM TICKETS) AS SQ

GROUP BY PRODUCT_TYPE
ORDER BY TOTAL DESC

Here is what I need the result to look like:
PRODUCT_TYPE DISPATCHED RESOLVED TOTAL
Access 23 20 43
Word 12 23 35
Excel 10 19 29
Powerpoint 9 19 28
Project 3 15 18
Others 20 23 43

Thank you in advance,

Jeff Johnson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top