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
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