I am trying to generate a very simple report with how many Critical, High, Medium, and Low tickets a person has using a sql query and just don't know enough about SQL to get what I want. 

I want the output to be close to this:

 

Users's Tickets,

LOW,3

MEDIUM,8

HIGH,2

CRITICAL,0

Here is what I have:

 

 SELECT HD_PRIORITY.NAME AS "Users's Tickets"
FROM HD_TICKET
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) 
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) 
WHERE (HD_TICKET.HD_QUEUE_ID = 21) 
AND ((HD_STATUS.NAME not like '%Parked%') 
AND (HD_STATUS.NAME not like '%Closed%') 
AND (O.FULL_NAME = 'user'))  
GROUP BY HD_PRIORITY.NAME
ORDER BY HD_PRIORITY.ORDINAL

The only thing I am missing is a second column with Count(*).

How do I do that? I feel like an idiot.

Thanks for helping me out.

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Now I am trying to create a query that would give me a output that would look like this:

    Tech Name,Nate,Peter
    High,1,5
    Medium,12,17
    Low,6,28

    Any help would be great. I am trying to create this for a custom Panic Statusboard graph.
Please log in to comment

Answers

0

An easy way to get this would be the following query:

 SELECT HD_PRIORITY.NAME, COUNT(HD_PRIORITY.NAME)
FROM HD_TICKET
JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.PRIORITY_ID
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
JOIN USER O on O.ID = HD_TICKET.OWNER_ID
WHERE (HD_TICKET.HD_QUEUE_ID = 21) 
AND ((HD_STATUS.NAME not like '%Parked%')
AND (HD_STATUS.NAME not like '%Closed%') 
AND (O.USER_NAME = 'user'))  
GROUP BY HD_PRIORITY.NAME 
ORDER BY HD_PRIORITY.ORDINAL

Note that I changed the query to username instead of full name since I find that more reliable.

Whenever you want to get a count of values in a column from MySQL you select the column, the count of that column, and then group by the column. 

 

Answered 12/13/2013 by: chucksteel
Red Belt

  • Thanks, that's exactly what I needed.
Please log in to comment
Answer this question or Comment on this question for clarity

Share