SQL noob Question
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.
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
11 years ago
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.
Comments:
-
Thanks, that's exactly what I needed. - nwhistler 11 years ago
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. - nwhistler 10 years ago